DAX-JUNGLE: Coalesce
It’s a jungle out there
Back in the day- when I was stuck on a DAX problem, I used to toggle through the IntelliSense in PowerBI one letter at a time. I won’t lie, it was pretty dumb.
A:
ABS
ACOS
ACOSH
…
B:
BETA.DIST
BETA.INV
BLANK
Etc….
Hours wasted..
Mistakes were made
A MUCH better use of my time would have been reviewing quality solutions to real world problems.
So that’s what we’ll do. Real problems, real DAX.
The Basics: Coalesce
According to Microsoft Themselves, Coalesce:
“Returns the first expression that does not evaluate to BLANK. If all expressions evaluate to BLANK, BLANK is returned.”
The function takes n number of arguments and evaluates them in order. Similar to a strange multi-tiered ISBLANK married to SWITCH, coalesce can help us handle errors and work around strange PowerBI ‘features’.
COALESCE(<expression>, <expression>[, <expression>]…)
After the first expression is evaluated, it check the results for a blank. A blank evaluation results in the next expression being called. A blank evaluation there result in the next expression being evaluated, and so on and so on.
Example 1: Error Handeling
Let’s explain our dataset. You run a fruit stand. You sell fruit and collect the numbers on a weekly basis.
| Fruit | Quantity |
|---|---|
| Apple.............. | 18 |
| Banana........... | 12 |
| Orange........... | 4 |
| Strawberry..... | 3 |
Because you sell fruit, you’ve got a weekly dashboard. A good-looking dashboard! But there’s one thing you don’t like: the big nasty ‘(Blank)’ on top of your fruit images.
The DAX measure we used here is pretty standard:
KIWI QTY = CALCULATE(SUM(Fruits[Quantity]), FILTER(Fruits, Fruits[Fruit] = "KIWI"))
However, it results in a blank evaluation when no Kiws were sold..
We can fix this by Adding a + 0 to the end of the like so:
KIWI QTY = CALCULATE(SUM(Fruits[Quantity]), FILTER(Fruits, Fruits[Fruit] = "KIWI")) + 0
But this results in a non specific formula, that’s where Coalesce come in:
KIWI QTY = COALESCE(CALCULATE(SUM(Fruits[Quantity]), FILTER(Fruits, Fruits[Fruit] = "KIWI")), 0)
or maybe you want a message displayed instead..
KIWI QTY = COALESCE(CALCULATE(SUM(Fruits[Quantity]), FILTER(Fruits, Fruits[Fruit] = "KIWI")), “No Kiwis Sold :(“ )
That looks way better! It’s very specific and outputs a clear ‘no data’ message.
Example 2: Logic Flow
Let’s say your manager was NOT very clear with your employees, and their hourly data has been recorded in all sorts of silly ways. Some people have recorded their hours, some employees recorded minutes, and James lost his mind and recorded his seconds worked.
We can fix this with DAX!
Actual hours Worked = COALESCE(SUM('Time Worked'[Hours]), SUM('Time Worked'[Minutes]) / 60, SUM('Time Worked'[Seconds]) / 3600)
This allows us to manipulate bad data so it makes sense!
If there are hours present, just sum them.
If there are no hours, check for minutes, sum them and divide by 60.
If hours and seconds are not present, sum the seconds and divide them by 3,600
The end result is hours worked across the board!
Summary
Coalesce helps us be specific with HOW we want to handle our errors and bad data. It allows us to do the best we can with the data we have. Coalesce will also help us keep our dashboards good looking ;). With coalesce in you toolbelt you’re ready to fight bad data for another day.
Our Latest Blogs
About FreshBI
Operating throughout USA and Canada, FreshBI is a boutique Microsoft Partner using Power BI to unlock the value trapped in your data so that your business can succeed.