The Logic behind the Magic of DAX Cross Table Filtering
28-07-2019 0 Comment(s)
Automatic cross filtering between columns of the same table or related tables is a very powerful feature of DAX. It allows a measure to evaluate to different values for different cells in a pivot table even though the DAX expression for the measure does not change. Filter context is the underlying mechanism that enables this magic behavior. But it is also a very tricky concept that even befuddles some DAX experts. Marco Russo and Alberto Ferrari have introduced DAX filter context in Chapter 6 of their book Microsoft PowerPivot for Excel 2010. Marco has also blogged about how Calculate function works. Recently I have run into many questions from advanced DAX users which tell me that people are still confused about how filter context works exactly. And this will be the subject of today’s post.
This post assumes that you already have basic knowledge about measures, row context, filter context, and DAX functions Calculate, Values, All, etc.
A level 200 pop quiz on DAX
If you think you already know how filter context works, let me ask you a couple of level 200 questions on DAX to see if you can explain the nuances of some DAX expressions. If you don’t feel like being challenged now, it is still beneficial to read the questions so you have some examples to better understand the following sections. The questions are based on the data model inside the publicly available sample PowerPivot workbook Contoso Samples DAX Formulas.xlsx. You can download the sample workbook to try out the formulas yourself if you want to, but it is not required to answer the questions.
People have heard that fact tables are automatically filtered by slices on dimension tables, but not the other way around, or in more general terms, if there is a relationship from table A to table B, A is automatically filtered by any slices on columns of B but B is not automatically filtered by any slices on columns of A. So if you select
DimProductSubcategory[ProductSubcategoryName] = “Air Conditioners”
on a pivot table slicer, measure
returns 62 as DimProduct is limited to air conditioners. On the other hand, if you select
DimProduct[ProductLabel] = “0101001”,
returns 44 instead of just 1 although only a single product is selected. To filter DimProductSubcategory by the selected product label, you can define a measure as
which returns 1. So it seems like when you explicitly add DimProduct as a setfilter argument of Calculate, DimProductSubcategory will be filtered by DimProduct. But if I define a measure as
to explicitly add the column that I know having a slice from the pivot table to the Calculate function , the measure formula returns 44 again. So what makes setfilter expression DimProduct work but Values(DimProduct[ProductLabel]) not work even though the filter only comes from [ProductLabel] column? If you think you have to add foreign key DimProduct[ProductSubcategoryKey] to the filter context in order for DimProductSubcategory to be filtered by DimProduct, you can try
but it still returns 44. If you have enough patience, you can use Values function to explicitly add all 33 columns in DimProduct one by one as setfilter arguments to Calculate function and you still will get 44 back. So what is the difference between table expression DimProduct and the enumeration of all 33 columns in that table?