DAX – Getting Distinct Count on Dimension table for Offices that have had Sales
I had a challenge when I was looking to get the Distinct Count of Offices only for the Offices that had Sales.
The reason for this measure is to get the % out of all Offices who had Sales.
Below is how I solved this.
What I did first was to create the following measure, the issue was that this measure had to filter the entire fact table which is very costly and consumes a LOT of memory to complete the filter.
This was the measure below.
CALCULATE ( DISTINCTCOUNT ( Office[Office Name] ), FILTER ( 'Fact Table', [Sales] > 0 ) )
To get this to run I did filter the data to a specific month of March 2022
When I looked at this using DAX Studio, I could see how it was being calculated using the server timings.
As you can see above, whilst it ran relatively quickly at 1.7 seconds it used 225 storage engine queries.
And if I had to run this over the entire fact table (300 million rows) it would error as shown below.
I had to find a way to get the valid number of Offices which would be fast and not error out.
To do this I used the measure below.
COUNTROWS ( GROUPBY ( 'Fact Table', 'Office'[Office Name] ) )
This DAX measure gives me the same result, this time I am using the GROUPBY to use the fact table “Fact Table” and to then use the relationship to count the rows for the number of Office Name.
As you can see this took 62ms instead of 1,750ms which is roughly 28 times faster. Not only that it used 1 storage engine query vs the 225 storage engine queries.
In this blog post I have shown you how using a different DAX measure can result in a massive increase in performance.
I must admit I really do enjoy doing some performance troubleshooting and finding a way to make it run a LOT faster.
Thanks for reading and if you have any suggestions or comments, please let me know! Have an awesome week.