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!
Got this question the other day:
I know how to report on the PRESENCE of a certain data element, but can I also report on its ABSENCE ? We’re working with healthcare data and have a list of patients who have had certain diagnostic tests. From this data it’s easy to find patients who have had a procedure (i.e., a mammogram), but can we use the same data to find patients who HAVEN’T had one?
I love it. Let’s dig in.
Let’s start with a simple Sales data example:
[Qty Sold] = SUM(Sales[OrderQuantity]
and ProductName comes from a separate Products table:
Our First Step
I write a new measure named [Did Not Sell]:
[Did Not Sell] = IF(ISBLANK([Qty Sold]),1,BLANK())
and that yields:
Cable Lock, Chain, and Front Brakes Did NOT Sell
And then I can remove the [Qty Sold] measure from the pivot so I just see the ones that didn’t sell:
Now We JUST See Things That Didn’t Sell
Where This Fails
That simple “IF ISBLANK” formula might seem too simple, and it is. Check out the grand total at the bottom of the pivot:
The Grand Total Row for this Pivot is Blank
Given our “IF ISBLANK” formula, that is actually “expected” – [Qty Sold] is not blank for that cell, so the IF() evaluates to false, and our [Did Not Sell] measure returns BLANK().
So let’s sum up the blanks instead shall we?
[Products that Did Not Sell] =
SUMX(Products, [Did Not Sell])
That formula iterates over each row in the Products table, summing up the [Did Not Sell] values it gets from each step.
In the case of a single-product row of the pivot, the formula only has one product to evaluate, so we get the same result as [Did Not Sell]:
For each single-product row, SUMX returns the same answer.
But for totals, it adds them all up.
So are we finished? Maybe.
Note 1: Careful about labels versus rows!
At the top of the pivot we see something disturbing:
AWC Logo Cap – Apparently DID Sell But Also DIDN’T? Didn’t sell TWICE actually – huh?
The answer lies in the Products table itself:
There are 3 Rows in the Products Table With That Name
One of those products sold a lot of units, two sold none.
What we DO about this is really up to the needs of the business. Maybe you leave everything alone, because you WANT to be told that there are two Product ID’s with the name “AWC Logo Cap” which have never sold.
Or maybe you change the formula to explicitly only tell you about entire Names that have never sold:
[Product Names that Did Not Sell] =
SUMX(VALUES(Products[ProductName]), [Did Not Sell])
In This Version of the Measure, AWC Logo Cap is NOT Counted as “Unsold”
For more on SUMX, see the Five-Point Palm Exploding Function Technique.
Note 2: This IS Specific to Products
Since the first input to SUMX in both examples above is “aimed” at the Products table, this will only be useful for counting products.
If you wanted to count, say, Customers who didn’t buy anything, you will need new versions of these measures that are “aimed” at Customers columns/tables.