“Unless I am wrong, and I am never wrong, they are headed dead into the Fire Swamp.”
A funny thing happened today. Kasper de Jonge asked a couple of questions (in comments) about the Introduction to DAX Measures post. I answered the “easy” question, and then went off to find the answer to the difficult questions.
Heh heh, I had them backwards. The “easy” question led to an in-depth discussion with Howie and Marius from the DAX team, during which I discovered an error in the “Qty per Day” measure.
I’ve uploaded fixed versions of the videos, so anyone watching for the first time (or re-watching now) will get the proper information.
And the workbook file available here is also fixed.
If you watched the vids already and don’t want to watch them again, here’s a quick summary of what I changed:
- Remember the golden rule, “Filter then Calculate?”
- Filter only impacts the “home” table (aka Fact table). So far so good.
- But the filtered home table is never then used to then filter other tables during the Calculate phase. Not even if you use RELATEDTABLE(). So for example:
- In my former example, I used COUNTROWS(RELATEDTABLE(DimDate)) as the denominator of the measure
- That expression, however, was returning the grand total number of rows in DimDate, no matter how the pivot was sliced. (Because of point 2 above)
- The fix was to use the [OrderDateKey] column from the FactInternetSales table (the home table) and do a COUNTROWS of the DISTINCT set of values in that column.
- Note that if there had been a slicer (or row/column field) from the DimDate table in the pivot, then the DimDate table would have been filtered, but:
- Not as a result of the Fact table being filtered
- It is strictly filtered only by fields in the pivot that came from the DimDate table
- And it is therefore much less filtered than the Fact table, which is filtered by all of the fields in the pivot
- Along the way I ran into the fact that the DISTINCT function requires an explicit reference to TableName[ColumnName], and does not accept the shorter [ColumnName] only syntax.
…yeah, the revised videos might still be worth a look 🙂