In my previous post, I introduced 3 Table Functions You Must Master. FILTER, ALL and VALUES.
The reason you must master them is simple: they provide the most common ways for defining the kind of table that will be fed into other DAX Table functions. As a matter of fact, ALL and VALUES often serve as one of the inputs used in even the FILTER function.
This post will be focused on FILTER and ALL functions using the Super Store Data. We will talk about VALUES some other time.
Recap of Filters Context
As much as possible, I will try to show you how you can look beyond the codes and try to mindfully visualize what the DAX engine is doing behind the scenes.
Let’s start by describing the manual way of filtering a data table.
- go to the desired table you want to filter
- click on the drop down of the column you wish to use for the filter
- apply the filter to be used.
In applying the filter to be used, you can either do one of the following:
- Check only the specific values
- Use the custom filtering option available
The Default Filtering
Assuming we are creating a report to visualize the sum of sales column from the Orders Table.
We can use the formula: SUMX(Orders, Orders[Sales]) to derive the sum of sales.
If you read my post on Thinking Like DAX – Understanding DAX filters context, Then you can explain how each value shown in the report below is derived.
You can tell that the values for each of the regions are calculated separately, after DAX separately carries out a filter on the table for each one of those regions.
If we translate this in the context of the formula SUMX(Orders, Orders[Sales]), it means that the first argument of the SUMX function (Orders), sees different things for each value calculated.
That is, when calculating the value for Central for example, the only thing visible on the Orders table is the orders coming from the Central Region.
And the same thing applies respectively for each of the remaining regions.
Therefore, the “Orders” you see in the SUMX formula is not the full Orders table, but the Orders table visible after DAX:
- went to the desired table it wants to filter (Orders)
- clicked on the drop down of the column it wishes to use for the filter (Region) 😀 😀
- applied the filter to be used (Region = Central, then for the next value: Region = East and so on)
Any time you refer to a “Naked Table” in a DAX formula, keep in mind that the table only refers to what is currently visible in the filter context.
This creates a limitation when trying to write calculations that need to use values outside the table visible in the current filter context.
Enter FILTER and ALL functions
The FILTER function provides a way to programmatically control the rows of table included in a DAX measure.
In the first argument, we provide a table. In the second argument, we explain how the table should be filtered.
The ALL function provides a way to ignore the current visible table by expanding the table from showing the current selection to showing all values.
It takes optional arguments. A Table name or column name(s). This will ensure that the report selection is expanded from the current selection to all values specified. (all values in the Table or all values in the column(s) specified)
Using FILTER and ALL functions in Measures
Let’s look at that report table of Sales by Region again.
The Sales values show correctly for each region because sum acts on the default filter context.
Let me give you two examples of things we can’t achieve because of the default filter context.
- Percentage of Consumer Sales from each region. Because the table visible to the current context includes all the segments in each region.
In our calculation for percentage of consumer sales, we must restrict the table further to contain only Consumer segment customers from each region.The formula required should behave like: Sales in the current region divided by Consumer Sales in the current region.SUMX(Orders, Orders[Sales]) will always show the sales in the current region.Because in this formula, the table Orders refers to all orders in a selected region.To get the value for Consumer Sales in the region, the first argument of the formula must change from the naked Orders, to a filtered version of the Orders. A version that has only Consumer segment. Therefore, the formula that will do this should modify the original one from:SUMX(Orders, Orders[Sales])to SUMX(FILTER(Orders, Orders[Segment] = “Consumer”), Orders[Sales])
- Percentage of each Region Sales to the Grand Total of all the regions.Because the table visible to the current context contains only the particular region selected.The table should behave something like: Sales in the current region divided by Sales in all the regions.SUMX(Orders, Orders[Sales]) will always show the sales in the current region. Because in this formula, the table Order refers all orders for the selected regionTo calculate the Grand Total, we must consider all the regions and not only the currently selected region.
To modify the filter from showing only the current default one, we must expand the Orders table visible from just current region to make all the regions visible.The formula that will get the Grand Total will modify the current formula from:SUMX(Orders, Orders[Sales])to SUMX(ALL(Orders), Orders[Sales])
Conclusion
We have seen practical uses of FILTER and ALL functions on Measures.
When we refer to a Table name directly within a function, the function will only see the naked table, which will contain only the current selections.
We can replace the naked Table name with FILTER function to define a different filter. The FILTER function will start from the currently visible table and filter based on the filter expression specified in the FILTER function.
We can also replace a naked Table name with the ALL function to ignore the current table visible in the current context to all values in whatever table or column we include in the ALL function.
Although there is another function regarded as the most powerful DAX function, CALCULATE function, that manipulates the default “visible table” behavior. But even the CALCULATE function always uses the FILTER function to complete the job.
In the next post, I will provide more examples of using FILTER and ALL functions with some where ALL is used inside a FILTER function.
