First of all: First!! Secondly, LEARNING that it's possible to create variables within an iterative calculation is very useful! I learn something from every SQLBI video I watch!
Great video. I thought I knew enough details about variables in DAX, but after watching this video, it's clear to me that there is always something more to learn.
Hey Alberto, Thank you for this great video, as always, it was very informative and helpful! I wanted to ask if you could do a video about VAR in row context and how it replaces the EARLIER() function. I kind of understand how this works, but the concept is still a little vague to me. I would appreciate such a video a lot! ❤ Thanks a ton!
Hey @thegreatlobz7422, The answer to your question is NO this is not happening in this case because there is no CALCULATE() wrapping that AVERAGEX(), therefore AVERAGEX() created a new row context over customer and for each row of the previous row context it is returning the value of the row context created by AVERAGX(). Stick with me I will explain this better. the SUMX() created a row context over customer lets give this row context the number 1 so its easier to understand. then inside of that row context created by SUMX() another iterator was called without context transition so it created a new row context inside of the previous one created by SUMX() Lets give the row context created by AVERAGEX() the number 2, so now we have two row contexts, row context number 2 is computing an AVERAGEX calculation inside of its own row context, and because context transition was not called row context number 2 is not filtered in the row context number 1, therefore the row context created by sumx doesnt have the average pro customer it has the AVERAGE over all customers. If we were to wrap the AVERAGEX in that case with a calculate then it wouldnt work, but at the same time if we were to add a REMOVEFILTER(Customer) as a calcualte parameter then it will work again because then we are removing the effects of CONTEXT Transition filtering customers. I hope this was a good explanation.
Just curious: in Python, you can constantly redeclare a variable's constant within a block (if it's initialized as a constant). I know you could technically just replace the variable's value wherever it's declared in the DAX, but if for whatever reason I wanted to redeclare the variable (say 'VAR Discount = 0.85' on one line and then 'VAR Discount = 0.75'), would the second declaration override the first declaration in the same manner it would in Python? Thanks in advance!
You can redeclare a variable in an inner scope - this way removing visibility from the outer scope - but you cannot assign a value to a variable after you defined it. A better name would have been CONST rather than VAR to be honest.
Thanks Alberto, I like at 11:00 when you demo using the same nested var name Result to show that the inner hides the outer. Didn’t know that. Can you tell me if there is another benefit beyond showing the separate evaluation of the two? or as you said, to stick w the same return naming pattern? Thanks!
Hey Alberto, thank you for the great content. I have a question, are all those Variables evaluated in this scenario? Count = VAR _days = VALUES( Date[Date] ) VAR _weeks = VALUES( Date[WeekNo]) VAR _months = VALUES( Date[MonthNo]) VAR _result = SWITCH( SELECTEDVALUE( someColumn), "daily", COUNTROWS( _days), "weekly", COUNTROWS( _weekly), "monthly", COUNTROWS( _monthly) ) RETURN _result Don't bother with the logic of the measure, just for demo purposes
Would it also be possible to use variables to create summarytable with filters and then apply rank on items in the sunmary table? I can't modify upstream data so all work has to be done with Power Query or Dax. I had somewhat limited success with calculated columns but cant apply a filter to a RANK
Usually in a report you want to see how many customers made purchases by date/product - we should call them "Active Customers in the current selection" but real estate in naming is very expensive so... "Customers" :)
"Why variables are not named constants?" "Nobody knows" 😂
First of all: First!! Secondly, LEARNING that it's possible to create variables within an iterative calculation is very useful! I learn something from every SQLBI video I watch!
Great video. I thought I knew enough details about variables in DAX, but after watching this video, it's clear to me that there is always something more to learn.
Thanks Alberto. Never knew could define and call variables in nested format!
I will never forget these examples!
newbie to DAX, and this video is very helpful. please do more, i'm watching all of your videos. Tks Sir !
Hey Alberto,
Thank you for this great video, as always, it was very informative and helpful!
I wanted to ask if you could do a video about VAR in row context and how it replaces the EARLIER() function. I kind of understand how this works, but the concept is still a little vague to me. I would appreciate such a video a lot! ❤
Thanks a ton!
This is gold, thank you so much Alberto!
14:30 good point, variables (VAR) are constants
At 17:40 wouldn't moving the AverageSales inside the iterator make it so that it calculates the average sales for that customer and not all customers?
It surprised me too. It looks like inner row context in SUMX overwrites the outer on same table Customers. But I've never heard about it before.
If that was a measure or wrapped inside a CALCULATE yes, but as shown in the video it iterates the full Customer table inside the AVERAGE X function
Hey @thegreatlobz7422, The answer to your question is NO this is not happening in this case because there is no CALCULATE() wrapping that AVERAGEX(), therefore AVERAGEX() created a new row context over customer and for each row of the previous row context it is returning the value of the row context created by AVERAGX().
Stick with me I will explain this better.
the SUMX() created a row context over customer lets give this row context the number 1 so its easier to understand.
then inside of that row context created by SUMX() another iterator was called without context transition so it created a new row context inside of the previous one created by SUMX()
Lets give the row context created by AVERAGEX() the number 2, so now we have two row contexts, row context number 2 is computing an AVERAGEX calculation inside of its own row context, and because context transition was not called row context number 2 is not filtered in the row context number 1, therefore the row context created by sumx doesnt have the average pro customer it has the AVERAGE over all customers. If we were to wrap the AVERAGEX in that case with a calculate then it wouldnt work, but at the same time if we were to add a REMOVEFILTER(Customer) as a calcualte parameter then it will work again because then we are removing the effects of CONTEXT Transition filtering customers.
I hope this was a good explanation.
when i had that scenario i avoided the variables, because they gave me the wrong result. now i understand better how to use them
Very helpful video as always! Thanks!
Just curious: in Python, you can constantly redeclare a variable's constant within a block (if it's initialized as a constant). I know you could technically just replace the variable's value wherever it's declared in the DAX, but if for whatever reason I wanted to redeclare the variable (say 'VAR Discount = 0.85' on one line and then 'VAR Discount = 0.75'), would the second declaration override the first declaration in the same manner it would in Python?
Thanks in advance!
You can redeclare a variable in an inner scope - this way removing visibility from the outer scope - but you cannot assign a value to a variable after you defined it. A better name would have been CONST rather than VAR to be honest.
Very nicely explained ❤
Long awaited video for me
This was very insightful, thank you Alberto
Thanks Alberto, I like at 11:00 when you demo using the same nested var name Result to show that the inner hides the outer. Didn’t know that. Can you tell me if there is another benefit beyond showing the separate evaluation of the two? or as you said, to stick w the same return naming pattern? Thanks!
It's a debug technique: www.sqlbi.com/articles/debugging-dax-measures-in-power-bi/
Thanks for a great video as always
Terrific video!
Hey Alberto, thank you for the great content. I have a question, are all those Variables evaluated in this scenario?
Count =
VAR _days = VALUES( Date[Date] )
VAR _weeks = VALUES( Date[WeekNo])
VAR _months = VALUES( Date[MonthNo])
VAR _result = SWITCH(
SELECTEDVALUE( someColumn),
"daily", COUNTROWS( _days),
"weekly", COUNTROWS( _weekly),
"monthly", COUNTROWS( _monthly)
)
RETURN _result
Don't bother with the logic of the measure, just for demo purposes
Yes.
So for time intelligence like SAMEPERIODLASTYEAR or DATEADD we should use the measure always instead of the Variable?
Would it also be possible to use variables to create summarytable with filters and then apply rank on items in the sunmary table? I can't modify upstream data so all work has to be done with Power Query or Dax. I had somewhat limited success with calculated columns but cant apply a filter to a RANK
The value of variables will be constant when used in calculated columns also ?
I am using a column in my calculated column variable
VAR == constant and =|= variable
I am very interested in how and why you would use error in the real world
An example is to stop a refresh using it in a calculated column or table when there is an issue in data.
@@SQLBI awesome thank you, I will look into this, didn't even occur to me
@@SQLBI seems my earlier comment got lost, but thank you, this is a brilliant idea
Why do you count the number of customers in the sales table and not the customer table? Is a customer without sales not a customer?
Usually in a report you want to see how many customers made purchases by date/product - we should call them "Active Customers in the current selection" but real estate in naming is very expensive so... "Customers" :)
@@SQLBI I understand.
The magister himself
excellent