A Power BI Technique Mined from the Power Pivot Archives
Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI. Hard to imagine - even for us - but true.
Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.”
Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables. Enjoy!
“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 🙂