“Unless I am wrong, and I am never wrong, they are headed dead into the Fire Swamp.”
-Prince Humperdink
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 
Really useful stuff, Rob, thanks very much.
In the spirit of your example I’ve made a PowerPivot-based chart that slices a set of values into groups, like so:
1) 0 < value < 200
2) 200 < value < 400
3) 400 < value < 600
I can make a count of values appear as totals underneath the sliced column of values, and the count does refresh when I use the slicer.
And I can reflect that count to a more convenient location next to the slicer.
But it looks like there's some event wiring that needs to happen in order to make the reflected count respond to the slicers?
Hi Jon, glad that it’s helpful
Hard for me to tell without seeing your workbook. Is it something you can send me, or perhaps just a screenshot?
-rob
To be a bit clearer on the strategy, I’ve added a PowerPivot measure defined as
=counta([columnname])
This produces a column of ones with a total under it.
Then in Excel-land I’ve got:
=SUMIF(F:F,”>1″)
Which captures the changing total. But the formula doesn’t recalc when the slicer changes the total, it has to be manually refreshed.
Follow-up: It was only, as you guessed, that recalc had been set to manual. I’m not sure how/why.
I am not (clearly) an Excel guru. But in way that’s exactly the point. In an era of widespread access to open public data, a whole lot of folks who are not Excel gurus will find themselves inspired to try stuff with PowerPivot.
Thanks Rob!