Getting Distinct Dimension Count Based On A Filtered Measure

Gogula Aryalingam shows us a neat trick in Power BI:

Enthusiastic as we were, one of the hardest nuts to crack, though it seemed so simple during requirements gathering, was to perform a distinct count of a dimension based on a filtered measure on a couple of the reports. To sketch it up with some context; you have products, several more dimensions, and a whole lot of measures including one called Fulfillment (which was a calculation based on a couple of measures from two separate tables). The requirement was to get a count of all those products (that were of course filtered by other slicers on the Power BI report) wherever Fulfillment was less than 100%, i.e. the number of products that had not reached their targets.

Simple as the requirements seemed, the hardest part in getting it done, was the limited knowledge in DAX, specifically, knowing which function to use. We first tried building the data model itself, but our choice in DAX formulae, and the number of records we had (50 million+) soon saw us running out of memory in seconds on a 28GB box; Not too good, given the rest of the model didn’t even utilize more than half the memory.

Click through for the answer.

Related Posts

Power BI Helper Version 2.0 Released

Reza Rad has a new version of Power BI Helper: I started Power BI Helper with the intention to help to find issues in Power BI reports faster and easier. This tool over time became better and better. I’m excited now to let you know that the version 2.0 of this product is now available […]

Read More

Flattening JSON In Power BI

Imke Feldmann shows how to flatten JSON data imported into Power BI: If you work with JSON documents that are new to you, it can be very helpful to fully expand JSON to see at a glance what’s in there. The following function has you covered for this task. It returns a table with all […]

Read More


November 2017
« Oct Dec »