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!
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.
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.
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
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!
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" :)
