Figure 1 View outside of my home office window May 2nd, 2018 Fairbanks, Alaska. Intro Hello, my name is Matthew Brice and I am guest broadcasting from my home office (via tape delay) in Fairbanks, Alaska. PPPro put out a…
Post by Rob Collie
In the Classic Physics Thought Experiment, Schrodinger Hypothesized a Cat That Was Simultaneously Dead AND Alive.
(But here we will use the more humane “Simultaneously Green AND Grey.”
Back to Basics!
PowerPivotPro.com celebrated its 5th birthday back in November. Over 5+ years, we’ve progressively covered techniques with an increasing level of sophistication. That’s pretty natural – we ourselves have become more skilled over time, AND there’s a tendency to not want to write the same post twice.
But today I want to drive home a basic point, one that will help “recruit” Excel pros into the Power BI camp, AND that will help “crystallize” a few things even for the longtime DAX practitioners.
Schrodinger’s Cat – A Classic Battle of the Nerds
In 1935, physicist Erwin Schrodinger wanted to show Albert Einstein how wrong he was. Einstein had recently published a paper that made an astounding claim about the nature of subatomic particles. If those claims were true, said Schrodinger, even “big” everyday stuff, like cats, could also behave in that same outlandish way. Which made Einstein look kinda silly, in Erwin’s mind.
He proposed the idea of a cat that was both simultaneously alive AND dead, and basically said “See, Albert? Alive AND dead is clearly impossible, so your theory is junk.” See here for details.
But modern quantum physicists actually think the cat experiment does NOT disprove Einstein’s claim. In fact, they think Schrodinger’s Cat demonstrates that the universe is fundamentally a MUCH stranger place than we typically think.
In short, the concept of “impossible” is subject to re-evaluation.
This PivotTable is Simultaneously Filtered AND Unfiltered
Post by Rob Collie
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:
When I was working recently with a client, helping her remotely – I asked her to calculate the sum for sales amount in the table. She responded whether she should use SUM, SUMX or CALCULATE?
Simple question, but not a simple answer. Or I can give you the classic lawyer response – “It depends!” Let’s review.
SUM: Simple Unmitigated Magic
The good news is that a simple SUM, would work in majority of the cases. A simple sum in the hands of Power Pivot is a powerful tool. With the magic of relationships a simple SUM can show you tricks you could never have imagined in Excel.
Power Pivot relationships mean, that you define your measures once and use them everywhere.
Your measures conform to the shape of your pivot, so you can drag and drop any fields from your model, use any slicers and the measures would still work.
Only when you see the results and go…”Uh…that is not exactly what I want”, should you explore other options.
Step at a time – SUMX
By Matt Allington In my last post I talked about a mistake I made early on in my DAX learning journey. In that post I showed a formula that used CALCULATE ( ) to turn a row context into a…
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”)
This 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:
FILTER(ALL(Territories[Continent]),Territories[Continent] = “Europe”))
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.