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.

Read the Rest

KPI’s in PowerPivot V2

 
PowerPivot KPI's in V2:  What Are They and How Do We Use Them?

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 Smile

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!

Read the Rest

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.

clip_image003

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.

Read the Rest

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:

Percentile Measures Update Figure 1

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.

Read the Rest

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… how to do that? The Obvious Thing Works for Most People Simple:  just uninstall v2 (from Windows Control Panel) and then install v1. But if you see this error when you try to build a new workbook, or open an…

Read the Rest

Microsoft’s PowerPivot Download Links Updated

 My former colleagues at Microsoft have responded to the issue I raised last week and have updated the PowerPivot.com download page to include links to both the “beta” of v2 as well as the latest, trusted version of v1. Still not perfect though, as the v2 link remains first And since not everyone knows that “2012 RC0” means “test version that won’t work with your servers, or anyone using v1” and that “2008 R2” means “version 1, the one you…

Read the Rest