Filtering, whether implicit or explicit, is integral to analytical reporting. This is because so much analytical reporting is about slicing and dicing. It's about making comparisons. Comparisons over categories, comparisons over time, and comparisons to the whole. And all of those require manipulating filters.
Implicit filtering is filtering that has been applied before the DAX expression is evaluated. This is often the result of user actions where in Power BI; they specifically click on a slicer visual, or in Excel; they perhaps click on the down arrow to filter things. Additionally, this is done implicitly by most visuals as well. Anytime you add a category to a grid or bar chart, the DAX engine is implicitly filtering based on that category. Explicit filters are filters coded into the DAX expression. For example, the CALCULATE function.
Using CALCULATE
Now here is the key point; explicit filtering overrides implicit filtering. So if the user, via action or report setup, has filtered all of the products on the colour black, the DAX expression can override that if it filters on the colour green instead. An example of an explicit filter using CALCULATE can be found below:
Sum of Green Products = CALCULATE(SUM(Sales[Total]), [COLOUR] = "Green")CALCULATE can take a measure or an expression and say whoa, whoa, whoa, before you evaluate. For the example above, we want to add up the total sales amount, but we want to make sure that the colour filter is always green, no matter what the user has applied. The first parameter specifies the expression we want to evaluate. The next parameter expresses the filter manipulations that we want to apply. In this case, we're overriding any applied filters on the colour column and filtering it to 'green' instead. CALCULATE can support multiple sets of filter manipulations, separated by commas.
Using FILTER
Sometimes you need to apply more advanced filters. The FILTER function, appropriately named, allows you to do that. There are a few specific scenarios when you would use FILTER. First, whenever you want to filter on an expression involving multiple columns or any kind of complex calculation, you need to use FILTER. CALCULATE, by itself, only lets you compare a single column to a fixed value. The second area where you need to use FILTER is when you want to compare to an aggregate function or measure. Let's say you want to filter products that have a price that's above average, given all of the products. Basic filtering and CALCULATE won't let you do that because that average needs to be dynamically calculated by looking at all the data. To do that, you need to use FILTER. Finally, for more advanced techniques, sometimes you need to return a table of filtered rows. That's what the FILTER function actually does; it returns a table. Now, why might you want to do that? Well, some functions require a table as an input parameter.
Measure Name = CALCULATE(SUM([Total]), FILTER(Sales, QTY * Price < 100))In the above example, much of the code is the same as before, we've got CALCULATE, and we've got SUM. What's changed is the second parameter of the CALCULATE function. Instead of a simple expression, we're going to use the FILTER function. The FILTER function requires two parameters. The first is the table we want to apply the filter to. The second parameter is the expression we want to use. Putting it all together, this expression will filter all of the entries in the Sales table to those rows that have a gross sales of less than £100.
Using All
One of the things that's different with DAX compared to Excel formulas or SQL is it's really easy to unapply filters. We can do this using the ALL function. This allows us to unapply filters on a specific column or an entire table.
As an example, let's say that you want to have a measure that shows total sales for the current context but is applied to all historical data, even if the user has filtered down to a specific year. This measure can be used as a comparison point for regular sales. To do this kind of analysis, we can use the ALL function.
Historical Sales = CALCULAE(SUM([Total]), ALL ('Date'))In the above example, we're going to apply ALL to the date table. A date table is used to manage data-based manipulations in accordance to filtering dates. This function clears any filters in the entire date table, including any of its columns and applies the CALCULATE function.
