Variables in DAX

Using variables in DAX makes the code easier to read, faster, and easier to debug. Learn how and when to use variables, along with why they are so important in any DAX expression.

[szklydm]

2 years into my Power BI / DAX journey, today you brought a new light into my life: variables are actually constants. Ha! Grazie, Alberto!

[myroslavamrochko]

I love you so much!!! Thank you for making my life easier! 😊 After an hour of finding the solution, I have found it here. 😊😊

[denissipchenko]

Thank you Alberto for this great tutorial video!

Personally for me it contains following important subtopics:
05:25 - nested variables
09:00 - common real use case for RELATEDTABLE function, where & why we should use it.
10:00 - restriction: you can NOT reference ColumnName inside Table variable.
11:45 - variable can not be blindly used as name for expression.

[randallevans]

The "Ciao, friends" is back!!! Have a great day Mr. Ferrari

[frsket]

I didn't know that a variable is only calculated once even when the filter context is changed. I'm sure this will save me from making a mistake at some point, thanks so much. 👌

[analistax]

You are always surgical in methodology. Congratulations.

[sachin.tandon]

Thanks Alberto! Amazing work and explanations!

[wkstmgw]

Hi Alberto ! Thanks for the tutorial.
Please clarify this.
While calculating Best Product Sales, why did you use nested SUMX. How is it calculated as it is summed inside already?
Would using calculate be better and give the same value?
Sales of Top 10 Products =
VAR top10 = TOPN(10, 'Product', [Sales Amount], DESC)
VAR Result = CALCULATE(SUMX ( Sales, Sales[Quantity] * RELATED('Product'[unit Price])), top10)
RETURN Result

[rjbush]

I think the key objectives here are readability and being succinct. Plain expressions are great for brevity but personally I like to see the expression broken down with VAR. It has the same process of defining and calling functions in other computer languages, and makes debugging easier. I was taught one function should return just on result.

[ofvcvvm]

Thank you & appreciate it @alberto ❤

[oleksiy]

Why we need to use SUMX (RELATEDTABLE ( Sales ), Sales[Quantity] * 'Product'[Unit Price] ) and not just [Sales Amount] ?

[josephansah]

I was thinking through an issue I was having with variables at the end of last week, and thought that your other variables videos could do with some additional and more thorough explanation. This video does the trick for 🏌🏽‍♀👌🏽Thank you 🙏🏽

[nehachak]

Hi,

In a visual, if I bring in the Products column and the Best products Sales, it gives me all the Products with the Total for only the Top 10 products.
However, when I use Best products Sales with Brands, i get the desired result. Can you please help to understand what's happening in the backend here? Why is my sales not getting restricted to only the Top 10 product's sale?

Thanks in advance
Best Regards,
Neha

[zxccxz]

the most confusing thing is Variable (essentially writing a measure that likely already exists). ie Total Customer
almost every example is writing actual measures as VAR

[frankief]

Thanks for this. There were a few mentions of performance but I can't see how variables can affect performance, does anyone have a link to explain the connection to performance?

[SergioTravieso]

Great vídeo, Alberto!

My questión is: is it better to use variables inside a measure or create a measure and use that measure inside other measures? Which is better for readibility and/or faster?

Thanks!

[gustavobarbosa]

Alberto, it’s early here in Brazil, so good morning!

You said that a VAR is evaluated only once during execution of the code. However, it seems to me that this is not always true.

When you show us that a VAR can be declared in other places, you declare two (Quantity and NetPrice) inside an iterator (SUMX). In this particular case, the two variables will be evaluated many times, one for each iteration. Am I correct?

Thank you!

[annalukina]

Is there a way to create a single var that can be referred by different measures? Also, if we have a measure that calculates, for instance, difference in sales between this year and last year. Can we somehow use the calculated result of this measure to calculate the YOY sales without sending a new query for the sales difference again in the YOY measure (considering both measures are in the same visual in the report)?

[RobertSmith-pfox]

Hello Alberto,
Thank you for an excelent video.
Would you plese show how to optimize the measure attached below?
The measure calculates over a few milions of records

VAR CurrentTradeID = SELECTEDVALUE('REPO'[Trade ID])

VAR MAX- =
ROUND(MAXX(
FILTER(ALL('REPO'); 'REPO'[Trade ID] = CurrentTradeID);
('REPO'[NC1]));0)

VAR TOTAL =
Notio]); ALLSELECTED());0)

RETURN
IF(
HASONEVALUE('REPO'[Trade ID]);
MAX_;
TOTAL)

[JuanFelipePalacioP]

In general, in programming languages ​​there is a culture to document above a function, the author, the parameters or the functionality of the code, is this used in DAX?