## DAX – Making the “Case” for SWITCH()

Guest Post by Colin Banfield [LinkedIn]

Way back during the period of the first CTP of Gemini (which later became PowerPivot), I was working with a data set that included a column of month numbers. I wanted to create a calculated column with month names, and the only solution that I could think of at the time was using the IF function in DAX:

=IF([MonthNum]=1, “January”,
IF([MonthNum]=2, “February”,
IF([MonthNum]=3, “March”,
IF([MonthNum]=4, “April”,
IF([MonthNum]=5, “May”,
IF([MonthNum]=6, “June”,
IF([MonthNum]=7, “July”,
IF([MonthNum]=8, “August”,
IF([MonthNum]=9, “September”,
IF([MonthNum]=10, “October”,
IF([MonthNum]=11, “November”,
IF([MonthNum]=12, “December”,
)
)
)
)
)
)
)
)
)
)
)
)

This a horrendous formula, with eleven nested IF functions and a long tail of closing parentheses. I promptly made a suggestion in the Connect forum for the addition of a “Case” function in DAX, siting the preceding formula as justification for its inclusion. Sometime later, I received a message from Howie Dickerman indicating that some form of Case function was being considered for PowerPivot V2 (the current version). This “Case” function turned out to be the SWITCH() function. “Switch” is a function familiar to Access and VBA users, so it made sense to stick with that name for the DAX  implementation.

## KPI’s in PowerPivot V2

A Shiny New Dialog to Play With!

### Better Late Than Never!

On Tuesday, in my intro to David Hager’s post, I promised to circle back “later today” and add some follow-on thoughts.  Well, ONE of those words ended up being truthful

But I’m back today!  Let’s not dwell on the past shall we?

In David’s post, he ended up with a “TestStatus” column set to Pass/Fail/Warning depending on a) what the test value reported  and b) the specific acceptable ranges established for that particular kind of test.

He was then able to use that as a slicer.  But what about conditional formatting?  His original example, a regular (non-pivot) table, DID include conditional formatting.

This is where I thought it would be good to finally check out the new KPI feature in PowerPivot V2.

### What is a KPI?

It stands for Key Performance Indicator.  Essentially though, what it means in practice is “a measure that has conditional formatting built into it.”

Pretty cool concept really – imagine being able to define your conditional formatting rule once and then use it any pivot with a single click!

## Conditional Formatting with Different Thresholds per Test, per Product, per Store, per Division, etc.

After a long hiatus, David Hager has returned with a new guest post.  He has a clever Excel trick/formula for applying different conditional formatting “acceptable ranges” depending on the context of the current row.  In his work, different Tests have different acceptable ranges of values that qualify as Pass/Fail/Warning.

Each Test Has a Different “Pass Range” and “Safe Range” –
David Hager’s Technique Translates This Table Quickly into CF Rules

I think this technique can be extended to basically anything:  an acceptable sales growth figure for Store A may be different than that for Store B (or Product A vs. Product B, etc.)

His post also got me thinking about the new “KPI” feature in PowerPivot V2, so I will return later today with a brief follow-on post.

## Percentile Measures in DAX – Errata

Guest Post by Colin Banfield [LinkedIn]

In September of last year, I posted two articles on creating percentile measures in DAX.  See Creating Accurate Percentile Measures in DAX – Part I and Creating Accurate Percentile Measures in DAX – Part II. About three months after I posted Part I, Richard Mintz left a comment indicating that he was having trouble getting correct results when his data sets had a wide range of values and many duplicates. I haven’t been receiving notifications when comments are left, so it’s purely by chance that I saw Richard’s comment recently.

During the process of building the measures, I did do some testing with duplicates, but the testing was minimal and involved only duplicates at the 25th or 50th percentile mark.

To check out the reported issue, I built a new data set with many duplicate values. Figure 1 shows the results of the percentile calculations in this scenario:

Figure 1

Huh? What’s going on here? The 50th and 75th percentile calculations are correct, but the 25th percentile calculation is totally and utterly incorrect! I created several intermediate measures along the way to the final result, so it was trivial to track down and correct the problem.

## Workarounds for “Canceled Due to Memory Pressure”

The Operation Has Been Cancelled Due to Memory Pressure

You can run into this error via a number of different routes, but one way or another, your computer ran out of memory while PowerPivot was trying to do something.

I’ll list some potential fixes here:

## PowerPivot V2 is Released!

Well the wait is over.  It is now released, for real.  No longer a beta or RC.  The real deal. Download it here. Other important links: My first glimpse at new features here. Important warnings about compatibility with V1.

## Downgrading From PowerPivot v2 to v1

OK, let’s say you already installed v2, whether inadvertently or…  advertently. And now you need to downgrade to v1, so that your workbooks function on your server, or can be opened by other people running v1 on their desktops. So……