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!
@gvitullib3 ай бұрын
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.
@druthorah3 ай бұрын
"Why variables are not named constants?" "Nobody knows" 😂
@sameerdeshpande82393 ай бұрын
Thanks Alberto. Never knew could define and call variables in nested format!
@TRZMac3 ай бұрын
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!
@cathrerinezetadrones31693 ай бұрын
I will never forget these examples!
@nevermore170119903 ай бұрын
newbie to DAX, and this video is very helpful. please do more, i'm watching all of your videos. Tks Sir !
@louism.49802 ай бұрын
This is gold, thank you so much Alberto!
@mwaltercpa2 ай бұрын
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!
@SQLBI2 ай бұрын
It's a debug technique: www.sqlbi.com/articles/debugging-dax-measures-in-power-bi/
@jyrrin2 ай бұрын
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!
@SQLBI2 ай бұрын
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.
@krisihari3 ай бұрын
Very helpful video as always! Thanks!
@TopnutritionCl3 ай бұрын
when i had that scenario i avoided the variables, because they gave me the wrong result. now i understand better how to use them
@kot233 ай бұрын
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
@SQLBI3 ай бұрын
Yes.
@kaselchirman99113 ай бұрын
This was very insightful, thank you Alberto
@GaryThomann-CoGC3 ай бұрын
14:30 good point, variables (VAR) are constants
@dineshs96703 ай бұрын
The value of variables will be constant when used in calculated columns also ? I am using a column in my calculated column variable
@drkamikaze13 ай бұрын
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
@HebertAG3 ай бұрын
So for time intelligence like SAMEPERIODLASTYEAR or DATEADD we should use the measure always instead of the Variable?
@thegreatlobu3 ай бұрын
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?
@rapacov3 ай бұрын
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.
@fernandoguajardo69133 ай бұрын
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
@TRZMac2 ай бұрын
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.
@ngoduyvu3 ай бұрын
Thanks for a great video as always
@santoshkulkarni9023 ай бұрын
Very nicely explained ❤
@timolff92393 ай бұрын
Long awaited video for me
@kennethstephani6923 ай бұрын
Terrific video!
@marcofestu3 ай бұрын
The magister himself
@MrSparkefrostie2 ай бұрын
I am very interested in how and why you would use error in the real world
@SQLBI2 ай бұрын
An example is to stop a refresh using it in a calculated column or table when there is an issue in data.
@MrSparkefrostie2 ай бұрын
@@SQLBI awesome thank you, I will look into this, didn't even occur to me
@MrSparkefrostie2 ай бұрын
@@SQLBI seems my earlier comment got lost, but thank you, this is a brilliant idea
@mabl43673 ай бұрын
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?
@SQLBI3 ай бұрын
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" :)