Distinct Count of Dimension based on a Filtered Measure

Okay, so we have this brand new awesome project going on; first time on Tabular model, and that too with a large number of measures connected to Power BI and all that jazz. The model would contain all those standard measures that the organization used, while we built some Power BI reports using these measures, with some visuals needing some improvisation on top of the standard measures, i.e. requirements specific to the reports.

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.

Using calculated columns was not a possibility since the columns that made up the measures that made up Fulfillment was from a different table, and the calculation does not aggregate up.

Since it was a report requirement, we tried doing it visually, by pulling in the Product and the Fulfillment fields on to a table visual, and then filtered Fulfillment, as so:

And then performed a distinct count on product, and voila! it performed a count, but alas! we realized that the filter gets disabled/removed when that happens. Which means the count always ends up being a count of all.

A frenzy of DAX formulae at the Power BI level did not help either, until we tried out the VALUES() function, courtesy direction from Gerhard Brueckl (b|t), trust the MVP community to have your back.

The VALUES() function returns a single-column table of unique values from a specified table. Hence using the FILTER() function we were able to extract the unique products where fulfillment was less than 100%, and then used the COUNTROWS() function to simply count the number of records returned.

Product Short of Target =
VAR ProductCount =
COUNTROWS ( FILTER ( VALUES ( 'Product'[ProductCode] ), [Fulfillment] < 1 ) )
RETURN IF ( ISBLANK ( ProductCount ), 0, ProductCount )

It is very easy to overlook VALUES as a function that would give you unique values. Hence why it is important that you have an overall understanding of what each DAX function can be used for, or at least get your hands on a DAX cheat sheet of sorts pinned against your wall. Glad this worked out though.

Advertisements

Doing the Forbidden: Fixing Cubes and Dimensions on Production et al.

Okay, now this could be a completely nuh-uh topic. But in the real world these things can and do happen. Example: There is this cube developed a couple of years ago, deployed on various environments such as QA, UAT, Production etc. Then something goes wrong when some new dimension data has text that is longer than what the field allows – ETL fails. You figure it is a small change to the dimension table: field QuarterName on table DimDate needs to be varchar(15) instead of the varchar(9) that it is. So you go fix it directly on the database – Big mistake when it comes to ALM etc. However, the ETL works. But, when you process the dimension on SSAS you get an error:

Errors in the back-end database access module. The size specified for a binding was too small, resulting in one or more column values being truncated. Errors in the OLAP storage engine.

Continue reading Doing the Forbidden: Fixing Cubes and Dimensions on Production et al.

Proactive Caching: Automatic Processing would not start

I had proposed using proactive caching for a near real-time cube, and the idea was that when the ETL was done every five minutes, proactive caching should automatically kick in to process the cube.

Seemed simple. Configuration was simple. And it worked as expected on development and QA environments. But on UAT, proactive caching would simply not start. Everything was configured just as it was on dev and QA. Permissions were also perfect, but for some reason proactive caching would simply not kick in to automatically process the cube once the underlying table was updated. The ETL runs every 5 minutes and takes only a minute to update the underlying table, so I knew exactly when silence interval should start telling the analysis services to start processing – yet nothing happened.

Continue reading Proactive Caching: Automatic Processing would not start

Now Everyone Can BI

If you had flown AirAsia, you would probably know where I stole the title to this post from. Microsoft did the same thing too. Heh heh! No, not the stealing bit, but providing for (almost) every small organization to use BI – Something which only the big bucks could afford.

Continue reading Now Everyone Can BI