From the Water Cooler As a 'water cooler' of sorts for this community, we meet some amazing people. Matt Mowrey shares with us a really useful technique to create a discoverable income statement using PowerPivot. This post runs a little…
Post by Rob Collie
Did Someone Say Deliberately “Misuse” a DAX Function for Our Benefit? We’re IN!
An End to Nested IF’s? Sign Us Up!
When we first saw the SWITCH function make its debut in Power Pivot a few years back, it was a “hallelujah” moment.
Whereas we used to have to write nested IF’s, such as this:
Now , with SWITCH, we could write that much more cleanly as:
Which do you prefer? It’s easy to make a strong “case for SWITCH,” isn’t it?
But What About Cases Other than Equals?
Now, let’s consider the following nested IF:
Notice that we’ve swapped out “=” for “<”.
And we can’t do that as a SWITCH, because SWITCH checks for exact matches between [Measure1] and 1 (or 2, 3, etc.)
This is unfortunate, because in these cases, we’ve had to keep using nested IF’s. And wow do I (Rob) *hate* nested IF’s. I can never seem to match the parentheses up correctly on the first try.