Tiny Lizard

Power Pivot Consulting and Training

  • About
  • Blog
  • Power Pivot
  • Training
  • Contact

VAR: The best thing to happen to DAX since CALCULATE()

July 11, 2017 By Scott Senkeresty 11 Comments

Image result for say hello to my little friend
Varface – Say hello to my little friend

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?

  1. 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.
  2. 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.
  3. I’m pretty sure this actually works Open-mouthed smile
  4. 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:
  5. [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.Image result for jar jar

  • About Scott
  • Latest Posts
  • Contact

About Scott Senkeresty

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 >>

  • 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

View All Posts

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 >>

DAX KEEPFILTERS Plus Bonus Complaining!
Review: Analyzing Data with Power BI and Power Pivot for Excel

Filed Under: Fundamentals, Performance, Power BI, Power Pivot

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 307 other subscribers

Comments

  1. Marco Russo says

    July 11, 2017 at 11:21 pm

    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!

    Reply
    • Oxenskiold says

      July 16, 2017 at 3:12 pm

      Here is a bold one: Can you really understand DAX without understanding EARLIER?

      Reply
      • Marco Russo says

        July 16, 2017 at 3:31 pm

        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.

        Reply
        • Oxenskiold says

          July 16, 2017 at 5:14 pm

          A wise one thanks!

          Reply
  2. Toufiq says

    July 12, 2017 at 4:10 am

    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.

    Reply
  3. Chandoo says

    July 12, 2017 at 6:38 am

    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.

    Reply
  4. Fred says

    July 12, 2017 at 3:21 pm

    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.

    Reply
  5. Brick Thompson says

    July 12, 2017 at 8:54 pm

    Excellent post, Scott. Thank you!

    Reply
  6. Oxenskiold says

    July 12, 2017 at 8:57 pm

    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.

    Reply
  7. iamnvt says

    October 27, 2017 at 7:03 am

    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.

    Reply
    • Scott Senkeresty says

      October 27, 2017 at 7:32 am

      This sounds like a fun blog post / question! Can u give more detail?

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Blog Posts

  • 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

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 307 other subscribers

Copyright © 2025 Tiny Lizard

MENU
  • About
  • Blog
  • Power Pivot
  • Training
  • Contact