Welcome to Power BI Antipatterns, my new blog post series! Over the past few years, as I’ve been working as an independent consultant, I’ve seen many examples of practices in Power BI that were less than optimal. In other words, they weren’t best practices. What’s curious is that sometimes very different people wrote code in the same (bad) way. So I thought I’d like to dissect some of the antipatterns, and I’m starting with the misuse of the SWITCH function in DAX.
Today I’d like to talk about the SWITCH(TRUE… pattern, first blogged about almost 10 years ago by Colin Banfield on what was then known as Power Pivot Pro: DAX – Making the “Case” for SWITCH() – P3 Adaptive. Consider reading Colin’s blog post for some DAX history. At the very least, check out Marco Russo’s comment:
It’s not easy to impress Marco!
Example 1
Here’s one pattern I sometimes see:
Example 1 bad =
SWITCH(
TRUE,
'Date'[MonthNumber] = 1, "J",
'Date'[MonthNumber] = 2, "F",
'Date'[MonthNumber] = 3, "M",
'Date'[MonthNumber] = 4, "A",
'Date'[MonthNumber] = 5, "M ",
'Date'[MonthNumber] = 6, "J ",
'Date'[MonthNumber] = 7, "J ",
'Date'[MonthNumber] = 8, "A ",
'Date'[MonthNumber] = 9, "S",
'Date'[MonthNumber] = 10, "O",
'Date'[MonthNumber] = 11, "N",
'Date'[MonthNumber] = 12, "D"
)Note: wondering why sometimes I have trailing spaces after month letters? It’s to allow sorting. Read more here: Single-letter day and month names in Power BI.
What’s the problem here? While the formula works and produces correct results, it’s less readable than in could be. More specifically, the 'Date'[MonthNumber] = bit repeats a lot while being completely unnecessary.
In my view, the SWITCH(TRUE… pattern here isn’t warranted, because the conditions are only equations against the same value (MonthNumber). A simple SWITCH would be more appropriate:
Example 1 good =
SWITCH(
'Date'[MonthNumber],
1, "J",
2, "F",
3, "M",
4, "A",
5, "M ",
6, "J ",
7, "J ",
8, "A ",
9, "S",
10, "O",
11, "N",
12, "D"
)Isn’t this much cleaner?
Note: to remove all instances of a text string, select the text, press Ctrl + Shift + L and delete:
For more keyboard shortcuts, see DAX formula bar keyboard shortcuts in Power BI Desktop – XXL BI.
Example 2
While in the example above the SWITCH(TRUE… pattern was unnecessary, sometimes we need it because we’re evaluating inequalities and the like. Here’s an example:
Example 2 bad =
SWITCH(
TRUE,
'Product'[Standard Cost] < 0, "Invalid",
0 <= 'Product'[Standard Cost]
&& 'Product'[Standard Cost] < 100, "Low",
100 <= 'Product'[Standard Cost]
&& 'Product'[Standard Cost] < 1000, "Medium", 'Product'[Standard Cost] >= 1000, "High",
"Unknown"
)What’s wrong this time? Remember, SWITCH evaluates conditions top to bottom. If a condition is false, SWITCH just goes to the next one and doesn’t look back. Therefore, if something isn’t less than zero, then it’s unnecessary to check if it’s greater or equal to zero — mathematically. Also — again, mathematically — the “Unknown” result will never be returned, because the previous conditions cover all real numbers.
Note: whether you want to catch errors, such as negative costs, in DAX is outside of scope of this blog post, because there are many factors that may affect your decision.
Assuming we do want to check for negative costs, here’s how I would write the formula:
Example 2 good =
SWITCH(
TRUE,
'Product'[Standard Cost] < 0, "Invalid",
'Product'[Standard Cost] < 100, "Low",
'Product'[Standard Cost] < 1000, "Medium",
"High"
)This formula is now much easier to read, in my view.
Sometimes having those extraneous checks can be dangerous. Check out the following formula from Colin’s blog post that I referenced above (DAX – Making the “Case” for SWITCH() – P3 Adaptive):
Ask yourself: if your fund balance is $10,000.50, what will be its size?
Spoiler alert!
The size will be “greater than $1,000,000”, because the first check is up to and including $10,000, and the next check is from $10,001. It would be much safer to write the formula in the following way (the size names would probably need to be altered a bit, though):
Fund Size better =
SWITCH (
TRUE,
[Fund Balance] < 0, "Negative",
[Fund Balance] <= 10000, "Up to $10,000",
[Fund Balance] <= 50000, "$10,001 to 50,000",
[Fund Balance] <= 100000, "$50,001 to 100,000",
[Fund Balance] <= 500000, "$100,001 to 500,000",
[Fund Balance] <= 1000000, "$500,001 to 1,000,000",
"greater than $1,000,000"
)Also, now that variables are available (they weren’t available when Colin wrote his blog post), sometimes it’s a good idea to store measures that are evaluated multiple times in SWITCH in variables to improve performance.
That’s all for today — hope you enjoyed!
Download the sample file: 1 SWITCH.pbix