I used to use the bad joke, “CALCULATE is the Ummmm of DAX” – like, when you want to start talking/writing out a DAX expression before you really know the answer, you just start typing CALCULATE slowly cuz you know it’s coming – it always does!
But now, I’m not so sure. Maybe I need a new joke that includes VAR. Maybe something with “Dude, Where’s My VAR?” or “Two priests walk into a VAR” ? Varship Troopers. Something VAR VAR Binks? “Ooh mooey mooey I love you!” VAR WARS! Wait, I think I might have forgot my ADHD meds, where was I?
The VAR keyword is amazing. It is available in both Power BI and Excel 2016, as well as SSAS 2016. “Yoosa should follow me now, okeeday?”
Easier to Write and Maintain
So, the boss-man walks up, looking smug like he is all smart and clever… and asks “Hey, do any of our products make up more than 2% of our total sales?”. Okay, we know some stuff… we can probably do that…?
Total Sales := SUMX(Products, Products[Quantity] * Products[PricePerUnit])
Big Sales := CALCULATE(COUNTROWS(Products), FILTER(Products, [Total Sales] > CALCULATE([Total Sales], ALL(Products)) * 0.02))
Does that work? I dunno… Maybe? Probably? It’s a bit complicated to read honestly… calculate the count of products … filtering the products for when their total sales is more than… some other darn calculation. But what if we rewrite it:
Big Sales :=
VAR TwoPercentOfSales = CALCULATE([Total Sales], ALL(Products)) * 0.02
RETURN CALCULATE(COUNTROWS(Products), FILTER(Products, [Total Sales] > TwoPercentOfSales)
Now, I dunno about you… but I find that WAY easier to read, write and understand. The ability to split the measure into more readable parts and to use more descriptive names (TwoPercentOfSales) in your expression makes it entirely more… um, expressive.
So… that first version of the measure… how many times does it calculate that two percent of sales? That CALCULATE([Total Sales], ALL(Products)) * 0.02) … is it calculated for EVERY Product from the FILTER() iteration!? DIOS MIO! Ya, that sounds bad. Real bad.
Well, guess what… the 2nd version (using VAR) makes it pretty darn clear we are only going to do that calculation one time. Hurray! WE ARE GREAT! TAKE THAT BOSS-MAN!
The more common place this comes up is when using an IF( ) statement to return BLANK( ) from a measure in some scenarios.
Say you have a measure that shows the % of sales compared to the prior year… but you don’t want to bother returning a value if you have no sales yet… (future months).
Sales – % vs PY := IF ([Sales], DIVIDE([Sales], [Sales – PY]))
So, notice [Sales] is in there twice… it is going to be evaluated TWICE! That is exactly HALF the awesome. But easy to fix:
Sales – % vs PY := VAR MySales = [Sales] RETURN IF (MySales, DIVIDE(MySales, [Sales – PY]))
Now the value of [Sales] is stored in the variable MySales… which is evaluated only 1 time. Way to go us!
Multi-Step Calculated Columns
For setup here, let me cheat and point you to http://www.daxpatterns.com/abc-classification/
The Italians are showing a lovely pattern to do ABC classification. But… there are *4* calculated columns involved? That makes me feel bad. I won’t want to feel bad. I like to treat my memory with the proper respect that it deserves! Can VAR help us here?
[ProductSales] = CALCULATE ( SUM ( Sales[SalesAmount] ) )
[ABC Class] =
VAR MySales = Products[ProductSales]
VAR ProductsWithSalesBiggerThanMine = FILTER( ALL ( Products ), Products[ProductSales] >= MySales )
VAR CumulatedSales = CALCULATE ( SUM ( Sales[SalesAmount] ), ProductsWithSalesBiggerThanMine )
VAR CumulatedPercentage = CumulatedSales / SUM ( Sales[SalesAmount] )
CumulatedPercentage <= 0.7, "A",
CumulatedPercentage <= 0.9, "B",
Wow. Okay, so fine… it got a bit complicated, but that’s okay… because I can make lists, and lists solve all the world’s problems, right?
- We are down to 2 columns. It’s possible to do it in 1 column, but it would not be fun, and I prefer fun.
- Notice the ProductsWithSalesBiggerThanMine variable. I specifically included this to make the point that variables can hold tables. They do NOT have to be just a scalar value, which is total sweetness.
- I’m pretty sure this actually works
- Wait, … when we were looking at the Italians formulas, I’m pretty sure we saw an EARLIER somewhere. right? Yep, I see it right there:
SUM ( Products[ProductSales] ),
ALL ( Products ),
Products[ProductSales] >= EARLIER ( Products[ProductSales] )
Umm. What’s up!?
The End of EARLIER
The first call I ever took as a consultant involved using EARLIER. I still have nightmares. I tried to describe its use here.
Say I wanted a calculated column that… I dunno, counts the number of products with sales higher than mine. Back in my day son, we didn’t have no fancy VARs, we wrote calculated columns like real men, and walked in the snow to school, uphill both ways….
Bigga = CALCULATE(COUNTROWS(Products), FILTER( ALL ( Products ), Products[ProductSales] > EARLIER(Products[ProductSales]))
But the earlier function just isn’t fun for noobs. VARs are! VARs are fun for everyone!
Bigga = VAR MySales = Products[ProductSales]
RETURN CALCULATE(COUNTROWS(Products), FILTER( ALL ( Products ), Products[ProductSales] > MySales))
I find that entirely more pleasant. No EARLIER( )… evar again.
Stuck in the Middle With You
Occasionally, you will find yourself writing a complicated measure… and find it’s useful to store a variable in the middle of a measure – and that is totally allowed! Totally making something up on the fly here… uh, Average Sales Per Day correctly handling leap year.
Sales Per Employee := AVERAGEX(VALUES(Stores), VAR Employees = COUNTROWS(RELATEDTABLE(Employees)) RETURN [Total Sales] / Employees)
Walk each store, store the # of employees in that store, … divide [Total Sales] in that store by the Employees in that store… than finally averagex those together. Is that different than just [Total Sales] / COUNTROWS(Employees) … probably not, but then, how would I show you a variable in the middle of an AVERAGEX?! Sheesh.
Okay fine, I couldn’t come up with a great example on the fly – but someday it will happen to you, and then you will thank me!
I think you can see why I am excited about the addition of VARiables to DAX. I doubt you will run into any situations where variables are somehow “required” – but I will say, I have come across situations where my measure isn’t working and I try throwing stuff into variables… and it magically works! Then people think I’m super smart, when really I’m thinking “Why the hell did that even work?!” then I’m like… oh whatever “… maxi big da Force.”
Definitely add this powerful weapon to your arsenal.
- The streak is alive! – August 10, 2017
- DAX KEEPFILTERS Plus Bonus Complaining! – July 20, 2017
- VAR: The best thing to happen to DAX since CALCULATE() – July 11, 2017
- Review: Analyzing Data with Power BI and Power Pivot for Excel – June 20, 2017
- Power BI Date Table – June 2, 2017
Scott Senkeresty here… Founder and Owner of Tiny Lizard. Yes, I have done some stuff: A Master’s Degree in Computer Science from California Polytechnic State University in beautiful San Luis Obispo, California. Over twelve years developing software for Microsoft, across Office, Windows and Anti-Malware teams… Read More >>