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.
Performance
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] )
RETURN
SWITCH (
TRUE (),
CumulatedPercentage <= 0.7, "A",
CumulatedPercentage <= 0.9, "B",
"C"
)
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:
[CumulatedSales] =
CALCULATE (
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!
Closing
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 >>
All good points – we know we have to review all of the DAX patterns using the new features in DAX, and VAR is probably the most important one! Be patient, now that we completed other things… this is very high in priority list!
Thank you for reminding me that we have still article out there using the legacy EARLIER stuff!
Here is a bold one: Can you really understand DAX without understanding EARLIER?
You’re right, but you don’t have to understand DAX 100% to use it – VAR simplifies life and it certainly makes the code much more readable.
A wise one thanks!
Great post!
Using VAR to create DAX queries for SSRS reports just makes life so much easier and faster. Add SUMMARIZECOLUMNS and SELECTCOLUMNS to that! we are so close to stepping away from “dynamic” (text concatenation) DAX.
Uphill both ways, really? Man, you walked the wrong way home.
Amazing write up. Didn’t know about VAR. I will use it at the next available opportunity.
Thanks for this post, Scott! I confess I don’t use VAR enough when I get the chance, but these tips I can apply right now.
Excellent post, Scott. Thank you!
I’m never really sure myself when VARs will actually improve performance. It’s in my opinion always difficult to predict and almost always takes some trial and test to make sure. The DAX engine caches results from calculations so in your example I would think that the CALCULATE([Total Sales], ALL(Products)) – without VAR – would be cached and only calculated once since the filter content never changes. Add to that the preference of the DAX engine to execute in block mode it then becomes difficult to say beforehand if those VARs will be of benefit. (Other than for readability which definitely is a good thing in it’s own right)
I would love to here if you or other people have other experiences.
hi,
for the last DAX, what is the difference between VAR inside, and outside of the X function?
I have an X DAX, where if I define the VAR before the X function, it will not result in the correct total, subtotal value.
However, if I define it is inside the X function, it works well.
This sounds like a fun blog post / question! Can u give more detail?