Check out THIS Bad Boy!
Old Tricks Combined With New
Folks, the combination of slicers and DAX is just… amazing. Do you SEE that picture above?
If the consumer of the report wants to change it completely, and see Top 10 Customers by Total Paid (Sales), it’s two quick slicer clicks:
How’d I Do It?
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:
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.
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!
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.
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:
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.