## A Star (Schema) and a SWITCH for Drill-Down Income Statement Design

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…

## DAX – The Diabolical Genius of “SWITCH TRUE”

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:

IF([MyMeasure]=1,expr1,
IF([MyMeasure]=2,expr2,
IF([MyMeasure]=3,expr3,…)))

Now , with SWITCH, we could write that much more cleanly as:

SWITCH([MyMeasure],1,expr1,2,expr2,3,expr3…)

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:

IF([MyMeasure]<1,expr1,
IF([MyMeasure]<2,expr2,
IF([MyMeasure]<3,expr3,…)))

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.