Turning “OR” Slicers Into “AND” Slicers

April 3, 2014


In this Report, We Are Only Seeing Customers Who Have Purchased
Both Accessories AND Clothing During 2004

A Post From Oceanside!

imageYeah, I’m on vacation (my first real vacation in 5+ years), so why am I writing a post?  Well, it’s before 9 am, the family is still sleeping in, and I honestly loved the idea of slipping out to write a post while looking at the ocean. 

The truth is I LOVE writing these posts – in some sense they represent Peak Fun for me, especially when they can be written at a relaxed pace with no outside pressures.  In the future, maybe I will take vacations for the express purpose of writing.  (That sounds surprisingly good to me actually).

Slicers – The More You Select, the More You “Get”

Read the rest of this entry »

CALCULATE(): More Examples and Reinforcement

April 1, 2014

Guess Post by Scott Senkeresty


Rob is taking a much-needed vacation this week, so you get to hang out with me again.  Hurray for you!

When we last Became One With Calculate, I said in comments that I would “work on a visualization/graphic”.  I admit to spending far too much time trying to dream up the perfect visual, and kind of failing.  I am sure the elusive visual exists, but for now, I would like to reinforce our understanding of CALCULATE() with a few more examples.

We will again be partying with the Adventure Works, against this simple measure:
[Total Sales] := SUM(Sales[ExtendedAmount])

Example 1: Column Filter

[TotalSalesEurope] := CALCULATE([Total Sales], Territories[Continent] = “Europe”)

imageThis boolean parameter (aka: true/false parameter, column filter) says “Hey, Mr Dax Engine, I really don’t care what filter you had on Continent… cuz now it is Europe”.   Of course, we did nothing that would impact a filter on Product[Category], so each of the categories still have their own total sales.

I must admit… when I last wrote about CALCULATE(), I was thinking there was something fundamentally different and special about these true/false filters, compared to the table-style filters such as we see with FILTER() or ALL().

And indeed, they are kinda sorta almost special… in that they have a cute syntax and they have the potential to be much more efficient (in terms of speed).

However, functionally, the above measure is identical to the following measure:

=CALCULATE([Total Sales],
FILTER(ALL(Territories[Continent]),Territories[Continent] = “Europe”))

Read the rest of this entry »

Becoming one with CALCULATE()

March 20, 2014

Guest Post by Scott Senkeresty

Intro from Rob

Hey, it starts out simple and powerful:  CALCULATE is the SUMIF you always wished you’d had.  It works in pivots.  It’s the “anything IF.”  It’s amazing, really, how many doors it opens.

Of course, CALCULATE is designed to be powerful in ways we can’t even IMAGINE in our first day/week/month of using it.  You can spend years discovering all the things it can do – and that’s a good thing!  But sooner or later you’re going to hit something with CALCULATE that makes you scratch your head – why is it returning THOSE results?

I myself entered this twilight zone with the Precedence Project – a series of posts that I quickly abandoned.  It turns out that, practically speaking, you don’t need to achieve deep theoretical understanding of this stuff in order to achieve great results.

Below, however, Scott does a great job of resolving those mysteries.  And he does so by “channeling” two old friends who live at the base of the Alps.  Take it away, Scott…

Going to “Graduate School”


All right, so I’ve read Rob’s book a few times.  (Heck, I am credited as tech editor on it.)  I’ve devoured PowerPivotPro University.  So now what, I ask Rob?

“Go forth and conquer – data is your ocean,” is his answer.  He’s a practical sort of guy.  Me, though?  I’m never satisfied until I’ve completely torn the machine apart.

So, as I hinted in my last post, I went to graduate school and spent a few intense days engrossed in Marco and Alberto’s book.


Read the rest of this entry »

Speed: Another Reason to “Trim” Calendar Tables

February 25, 2014


An 11,000-Row Calendar Table Spanning from 2000 to 2030:
Most of the Time This is Harmless Overkill

A 60x Speed Improvement From a Most Ordinary Place

I’ve been doing some work lately for a client who really pushes the DAX envelope.  One of the top-three models I’ve ever worked on in terms of complexity, no doubt.  And really, my role is just to help fine-tune it and add a few bells and whistles.  They built this sucker themselves and I am way impressed.

Crazy stuff.  Formulas that use outlier dates from one Data table (“fact” table) to then subsequently filter another Data table (via its related Calendar table), but then wrap that up inside a MAXX inside a SUMX…  and it all makes perfect business sense.  It’s magic.

But speed ain’t its strong suit.  We tried all the usual tricks – “de-importing” unneeded columns, replacing calculated columns with imported versions, etc.

And it was still way too slow.  Then we tried something even simpler, and things got 60x faster.

Read the rest of this entry »

Optimal Set Selection, Power Pivot Plays Pokemon, and My Brain Just Exploded

February 13, 2014


Un.  Freaking.  Believable.
(See bottom of post for video of this in action!)

Back to Our “Regular” Programming Today

First of all, THANKS for being patient with the promos for PowerPivotPro University – we do have to pay the bills here of course, AND I have poured six months of my best work into it, so thanks for giving it your consideration.

But yeah, it’s time to get back to some serious magic tricks with data.

A Funny Thing Happened On Reddit and Twitter…

I don’t play Pokemon – neither the video nor card game variety.  But I recognize my kind of sickness (cough cough fantasy football) when I see it.

And people who know me, ALSO know my sickness.  So they point things out to me:

And that led me to this Reddit thread:  Pokemon & Power Pivot…

Read the rest of this entry »

“CONTAINSX” Revisited: What WAS the Match?

January 28, 2014

Building on a Popular Technique

Power Pivot Substring Match/Contains Grouping Column

Last week’s post on “CONTAINSX” proved to be quite popular.  In the comments, Sasha provided an alternate formula that used FILTER instead of SUMX.  Honestly I have a been of a “fetish” for SUMX – after all it IS the 5-Point Palm Exploding Function Technique – so at first I was like “nice work Sasha but I’m sticking with my SUMX.”

But then “en” asked if we could write a formula that reported what the matching keyword actually WAS – not just whether there WAS a match.

And then, Sasha’s formula came in super-handy.  A couple of quick mods and we were in business.  Read on for the formula, but first, a quick aside.

Another thing that is easier in Power Pivot than “Traditional” Excel

I really enjoyed the comments (and the emails) we received about the CONTAINSX post.  Here are two of my faves:

Read the rest of this entry »

Defanging the “contiguous date selections” error

January 16, 2014

Calculation Aborted.  Function DATEAD only works with contiguous date selections.  Power Pivot, why you gotta be so cryptic?

Have You Ever Seen This Cryptic Error?  Here’s a Quick Explanation and Fix Smile

So you’ve got a DATEADD measure…

Let’s say you’ve written a nifty measure called [Last Year Sales] that returns the sales value you had one year ago at the same time:

  [Last Year Sales] =

  CALCULATE([Total Sales, DATEADD(Calendar[Date], –1, Year))

And hey, that works great:


So far so good:  [Last Year Sales] Measure is Working Great!

Read the rest of this entry »

Cumulative Interest or Inflation, Multiplying Every Value in a Column, Why Don’t We Have PRODUCTX?

November 4, 2013

Cumulative Inflation/Interest in Power Pivot - Who Needs PRODUCTX?

Given the First Two Columns at Left, How Do We Calculate the Two Columns at Right?
(It’s 1.14 * 1.18 * 1.08 * 1.08…  How do we do that?)

I Take No Credit for the Question Or the Answer!

So much coolness and none of it is mine.  Today I am merely a messenger.

First of all a great question came in on an old blog post (the one about Moore’s Law and Inflation):

HOW do you perform cumulative inflation calculations in Powerpivot. What seems trivial, multiplying all the values in a column together has me stuck, as there is no PRODUCT() formula. The only way i can think of doing it is through messy iterations but that can’t be it.

Awesome question Andy!  And I was stumped.  How do we multiply every number in a column together, which is what we need to do here?  I mean, if we had a PRODUCTX() function I’d know where to begin.

To the Mother Ship (Microsoft) We Go!

I think the world needs more X functions (I’m serious, that’s not a joke, although it IS a funny thing to say, so I say it often). 

So I took this opportunity to ask my former colleagues at Microsoft – can we have a PRODUCTX function in the future?  And is there a way to “fake it” in the meantime?  Spoiler alert:  the answer is yes.

Jeffrey Wang is One Smart Dude

Read the rest of this entry »

Reverse Polarity RELATED()

October 3, 2013


Guest post by Ken Puls

I was recently working on an interesting thing in PowerPivot, and thought I’d share it in case it may help someone… A quick summary of the issue is that I needed to examine multiple records in a child table, and return a single result to the parent table if a condition was present; something that goes against the normal relationship flow.

Scenario Background

I have a file that tracks the purchase and sale of land, as well as the respective property taxes and assessments that we get on an annual basis. It is set up like this:

9-17-2013 2-56-59 PM

At first glance this might look complicated… Each Parcel is assigned a unique Parcel Identifier, known as a PID, a key identifier that will never change for a piece of property.  This info is then used to identify that parcel by the tax authority, the assessment authority, and our appraisers and accounting systems.  In the case above, we can see that the PID from the Parcel table links to the Transaction, Taxes and Assessments tables. From the other side we’ve got the Key_Date that links back to the three tables as well, although it links to TranDate, TaxYear and AssessYear respectively.

Read the rest of this entry »

Standard Deviation Demystified in Power Pivot

October 1, 2013

Anakin asked questions about Power Pivot's STDEVX.P

“Only a master of evil formulas, Darth!”
”…hmm, which kinda DOES make you the master doesn’t it?  Crap, I give up, just
chop me in half and get it over with, but make sure Luke sees you do it ok?”

Anakin’s Second Line of Questioning

Two weeks ago I posted about a particular student’s knack for making me learn new stuff, specifically the “bubble up ranks” method.  (Ooh, I forgot to circle back and cover the “bubble up exceptions” part – making a note of that now).

Anyway, he also pressed me to teach STDDEVX().  That’s a first.  “No worries,” I said, “it’s just another X function – but only you will know if the numbers are correct, because I don’t know what Standard Deviation truly means.”

Embarrassing isn’t it?  To be a numbers guy and not know what Standard Deviation means?  I was on the varsity calculus team in high school.  I even have a Math major (to go along with my Computer Science and Philosophy majors – jack of all trades, master of none).  How did I slip through the cracks on Standard Deviation?

OK yeah, it measures deviation, variability.  I get that from the name.  And yeah I even “learned” it in Statistics class in college, to the extent I ever showed up at 8 am.  But I didn’t grok it.  Didn’t breathe it in deeply.  Until now. 

It’s all fun and games until the Football Project Returns!

Read the rest of this entry »

“Normalize” Your Measures for Fun and Profit!

September 26, 2013


Can YOU Spot the Green or the Red Line?  (Bonus Q:  Can You Tell
That the Green Line is 50x higher than the Red Line?)

Look Ma!  Two Posts!

After failing to maintain my Tuesday & Thursday posting schedule over the past two weeks (one post total, out of four scheduled), I’m BACK folks.  I mean this week didn’t stand a chance.  BAM!  Nailed it.

OK, enough self-congratulation for meeting the minimum standards I set for myself.  Moving on!

Three Different Scales

The three lines plotted on the chart above are “sourced” from these numbers:


These Three Measures Are VERY Different in their Relative “Sizes,”
Varying From 2 Digits to 6 Digits.

Data like that results in crappy charts.  Let’s fix it with some formula magic:

Converting Values to Equivalent Scales for Charting - another Power Pivot Magic Trick!

Ah, Formulas Make Everything Better.  Yep, it’s the same data, just “normalized.”

A Word from the Charting Pit of Derision!

Yeah, I hear that chittering out there – the mandibles of the Demonspawn Chart Fiends are clacking out a sound that resembles “Secondary Axis!”

Read the rest of this entry »

Ranks and Exceptions that “Bubble Up” to Subtotals

September 17, 2013


One Training Session, Two (Three?) Blog Posts Inspired

Every now and then, I find myself training someone who I just KNOW is going to be teaching ME things in the near future.  The questions they ask are the dead giveaways.  And the next time we meet, they will be Darth Vader, telling my Ben Kenobi about how the circuit is complete.

I had the pleasure of teaching someone like this quite recently.  He forced me to write some pretty nifty calcs, or at least some that *I* think are nifty.

The Formula

Read the rest of this entry »