The Diabolical Genius of “SWITCH TRUE”

March 31, 2015

Post by Rob Collie

SWITCH TRUE Alternative to Nested IF's

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.

But There’s a Sneaky Antidote!  We CAN Still Use SWITCH!

Read the rest of this entry »

Repeat Customers in DAX: Three Flavors

December 9, 2014

Post by Rob Collie

Repeat Customers in Power Pivot / DAX:  By Number of Transaction Lines, By Number of Distinct Orders, and Allowing for Cross-Year Return Customers

In 2004, There Were 2,561 Customers Who Bought Something in the Southwest.
But How Many of Those Were Repeat Customers?  Depending on How We
Define “Repeat,” We Can Get at Least Three Different Answers.

A Right Turn at Albuquerque…

I sat down today to write about “Disconnected Cube Formulas” – yes, you heard that right.  A brand new technique that I think has some pretty exciting (yet admittedly narrow) applications.

But along the way, like Bugs Bunny, I ended up doing something at least as interesting.  So let’s do that one first.

Setting Up the Problem

I have four relevant tables:  Territories, Customers, Calendar, and Sales:


The first three are Lookup (aka Dimension) tables, and Sales is a Data (aka Fact) table.

Active Customers is a pretty easy formula:

  [Active Customers]:=


Read the rest of this entry »

Displaying Top N, Bottom N, and “All Others”

November 11, 2014

Post by Rob Collie


If We Use Excel’s Built-In Top N Filter to See Our Top 1,000 Customers, It Hides the Other Customers Completely.  But Using DAX, We Can Just “Split” the Audience into Two Groups.

This Came Up Recently…

Hey, I absolutely ADORE the TOP N filter capability offered by all Excel Pivots.  It kicks major booty and we use it all the time:


The Top 10 / Top N Value Filter in Pivots:  Get to Know It, It Does Amazing Things

But If we set that to show us the top 10 customers, it shows us JUST those 10 customers:


OK cool, we see those top ten customers, and that they collectively purchased $132,026 of stuff from us.

But we want to ALSO see how much the OTHER (non top 10) customers are worth too.

Read the rest of this entry »

Blending “Time of Day” Analysis with Calendar/Date Analysis

October 7, 2014

Post by Rob Collie
Blending “Time of Day” Analysis with Calendar/Date Analysis in Power Pivot and Power BI

Our “Morning” Website Traffic is Down 21.5% in Jan 2014 vs. Jan 2013, But “Evening”
Traffic is Up by a Similar Amount, and Full-Day Traffic is “Flat” at +0.9%

(Fake Data, But Real Analysis)

Two Different Flavors of “Time”

Usually, when we talk about “time” in Power Pivot, we’re talking about the Calendar/Date flavor:  How much have things changed from yesterday to today.  What are our Month to Date numbers, and how do those compare against the same period last year?  Let’s call this “macro-trending.”

But time of day is also often interesting:  what are the trends WITHIN a day?  Let’s call this “micro-trending.”

And then, the hybrid of the two:  how are our “micro” trends changing over the course of the year, month, quarter, etc?

I don’t think the techniques here are terribly complicated, but they might be a little difficult to conjure up on your own.  So, it’s time for a post – and a downloadable workbook! Smile

The Key:  Separate the Date and Time Components!

Read the rest of this entry »

5 Interactive Chart Techniques Come Together

October 2, 2014

by Matt Allington

Recently I have been building some interactive charts for one of my clients using techniques that I have learnt from, from searching the Web, as well as some of my own ideas.  While some of the techniques I will talk about in this post are not new, I have combined some of these old favourites with some new techniques to solve some of the problems I have come across. I want to illustrate how the combination of these things can deliver a very positive user experience, and just as importantly – anyone with a good set of Excel skills can build an interactive charting tool like this by following the patterns demonstrated.  I have created a demo of all of these concepts into a new workbook using Adventure Works so that you can see how these techniques come together for the user.  There is a link to this workbook at the end of this post.

The techniques I have used are:

  1. Disconnected slicers used to create interactive chart series
  2. Cube formulae and standard Excel to make an interactive chart title

I love these 2 tips I learnt from Rob – so user friendly.  However I came across a few problems when I tried to implement these, hence I have developed the following 3 additional techniques to solve these problems.

  1. Cube formulae and standard Excel to make an interactive legend
  2. VBA and “link to source” for interactive axis formatting
  3. Excel VBA to change which Axis the series appears on.

I have created a short video to demonstrate the 5 features built into this workbook, and I then explain each of these in more detail below.  I have not hidden the behind the scene workings so you can see these in action – of course you would normally hide these from the user.

Now let me call out the key techniques I have used to make this workbook rock.
Read the rest of this entry »

Dynamic Charting In Power Pivot

August 28, 2014

Guest Post by Idan Cohen From Excelando

Rob already blogged about charts with dynamic measures -Using Named Sets for “Asymmetric” pivots, where you can choose the measures to be displayed with a slicer.

But what about dynamic axis?

This clever technique was found by one of my analysts , Gal Vekselman, when a client challenged us.
What is it useful for you ask ??!

For example, I want a chart to display sales by quarter and then change it to sales by month with a press of a button?
Or another useful scenario where I want to see sales by category,  but when choosing a category on the slicer,  the sub categories for this category will be displayed in the chart,  and when choosing a sub category the chart will display the underlying products.  Sounds cool, huh? Well,  it is even cooler.

And the way to do it? Named sets!

Dynamic Monthly Chart Dynamic QuarterlyChart

Use the period selection slicer to quickly change the chart axis, from Month to Quarter

Read the rest of this entry »

SUMX of IF: A Perfect Blend of Simple & Sophisticated

August 26, 2014

SUMX of IF Used to Make Grand Totals Add Up in Power Pivot DAX

In This Case, Getting the Grand Total Correct for Each Row Required SUMX

It’s that time of year again…

…when my love of spreadsheets actually translates into a love of sports.  Yes, it’s Compulsive Data Crunching Disease season, AKA Fantasy Football Season.

Fantasy football is a game in which the contestants assemble “portfolios” of NFL players in the same manner that you might build a portfolio of stocks and bonds.  Then your portfolio (we call it a “team”) performs well if the real-life NFL players perform well, and poorly if not.  The one difference between this and the stock market is that no two “portfolios” can contain the same NFL player – so if I get Peyton Manning, the other contestants in my league (typically 8-12 people) cannot have him.

I’m participating in a new form of league this year, one in which the contestants get to keep some of the players from prior years.  (In most fantasy football leagues, you start each year from a clean slate).

We’re going to be picking our players this weekend at an “auction” or “draft,” and naturally, I want to scout my opponents ahead of time.  Muhaha.

So, what do my opponents need?

A valid portfolio consists of:

Read the rest of this entry »

The Many Faces of VALUES()

August 21, 2014

Guest Post by Scott at Tiny LizardMany Values

Maybe it is a sign of where I am on the Geek Scale compared to Rob, but where he considers EARLIER() to be a pretty hard function to understand, it just doesn’t bother me.  At least it seems to have just one purpose in life.

Now, the VALUES() function on the other hand… well, that’s just some messed up stuff right there!  Not only does nothing about it feel natural and intuitive to me, but it also seems to behave in completely different ways depending on how and where it is used.

Basically, every time I use it, I feel like I either got lucky, pulled a fast one, and that I’m a dirty cheater.  So, at least I got that going for me.

Let’s look at some of the various usages.

Read the rest of this entry »

Toggling Between Different Units via Slicer?

July 29, 2014

Is this possible?

Someone at Microsoft asked me this question the other day:

“Sort of like how you’ve used a slicer for conditional formatting, is it possible to use a slicer to change the custom formatting of a number?  In my use case, I want to be able to display currency as either full number ($1,500,000.00) or abbreviated ($1.5M) as the viewer wishes.  See below for an example of the desire.”

Use a Slicer to Change Number Formatting from Raw to Millions/Thousands M/K?

Can We Do This in Power Pivot?

My Answer:  No, not possible.  Wait, maybe.  Hmm.  OK, yes, mostly.

All of these thoughts flashed before my eyes:

  1. Power Pivot measures/calc fields must always have a consistent data type.  You can’t have a measure return numbers sometimes and text other times, for instance.  All “exits” from an IF or a SWITCH must have the same data type.
  2. Apparently, #1 is no longer true in SSAS Tabular, in the 2014 release.  They now support “variant” data type measures. 
  3. But no, Power Pivot still lacks that “variant” measure capability, at least for now.
  4. Whoa, hold on a second.  The desired result above does NOT use different data types!  It’s all numbers!  So we just need to change the math!
  5. Oh, ouch, not so fast.  The “M” and the “K” – I don’t know how to add those labels in a numerical data type.

So this means…  text measures!

Read the rest of this entry »

FrankenSpark! (Cube Formulas Meet Sparklines)

July 22, 2014

Cube Formulas in Power Pivot Combined with Sparklines:  AKA FrankenSpark

That’s a Single Spreadsheet Cell with a CUBEVALUE Formula AND a Sparkline in It!

I was working with a client last week when a question occurred to me:

“Can I put a Sparkline in a cell that already has a Cube Formula in it?”
”Oh cool, it worked!” (Cackles Maniacally)

-me, last week

Anyway, we were off and running at that point:

Cube Formulas in Power Pivot Combined with Sparklines and a Chart

“FrankenSpark” Used as Part of a Larger (Redacted and Obfuscated) Client Scorecard
(Yes, the Colors Still Need Some Work)

The How-To

Read the rest of this entry »

We Have a “Crush” on Verblike Reports

June 24, 2014


Even Very “Sophisticated” Reports/Dashboards are Often “Couch Potatoes” in Practice
(They Sit There and Expect You to Do All the Work)

Intro From Rob

I’ve been meaning to blog this for a long time – it is, after all, one of my absolute FAVORITE things to talk about.  Now, Scott beats me to it.  But he does such a good job below that I don’t have much to add, except a few graphics here and there, like the one above.

Besides, how can I not love a blog post that starts off talking about how right I am? Smile

Guest Post by Scott at Tiny Lizard

By nature, I am a rather skeptical person.  When I first hear an idea, I generally think it is probably wrong.  This isn’t one of my finer traits and  I am sure it drives Rob insane, since he has a rather impressive track record of being correct.  Thankfully, this blog entry is not about one of the times I assumed Rob was wrong…

It is actually the complete opposite.  It is one of those times when Rob told me something and it just instantly SUPER resonated with me and got stuck in me.   I suspect it will do the same for most of you.

Nouns and Verbs

imageIf you were fortunate enough to hear Rob speak recently at PASS Business Analytics Conference, you have already heard this idea.

It is super typical for us, as report authors, to generate The Report.  The Noun.  We hand it off with pride.  And The Report has all the information somebody could possibly need to make a decision.  All of it.   Row after Row. Column after Column.  Unfortunately, even with the fanciest conditional formatting in the world, it is not clear… how is somebody supposed to look at The Report and actually… do something?

Because, at the end of the day (and yes, with a handful of exceptions like keeping the SEC happy), it is our hope that somebody looks at our reports and uses it to make a decision.  To do something.  To Verb.

Read the rest of this entry »

Avoid Constant Refreshes in Excel 2013

June 5, 2014

Guest Post by Scott at Tiny Lizard

Power Pivot 2013 is a bit, shall we say, aggressive with its desire to keep your reports up-to-date.

Renaming a measure that isn’t even used yet?
Please wait, while I refresh your reports.

Adding a brand new measure that can’t possibly be used yet? 
Please wait, while I refresh your reports.

You tilted your head to the side?
Please wait, while I refresh your reports.

I had a customer with 16 pivot tables per sheet, and about 16 sheets.  Making edits was getting very non-fun, so I finally took the time to look into this fantastically stupendously wonderfully awesome solution to the problem.Untitled_thumb[4]

It worked ok. Nyah-Nyah New entries get added to your pivot table context menu, as seen on right.  Simply disable when you intend to make a bunch of edits at once, and use the extra time to read Rob’s new book.

Highly recommended for those annoyed by this in 2013.