When are variables evaluated in DAX

Поделиться
HTML-код
  • Опубликовано: 22 дек 2024

Комментарии • 44

  • @ZiggyBoon
    @ZiggyBoon 5 месяцев назад +5

    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!

  • @gvitullib
    @gvitullib 5 месяцев назад +2

    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.

  • @druthorah
    @druthorah 5 месяцев назад +15

    "Why variables are not named constants?" "Nobody knows" 😂

  • @sameerdeshpande8239
    @sameerdeshpande8239 5 месяцев назад +3

    Thanks Alberto. Never knew could define and call variables in nested format!

  • @Ahmed-e4w1e
    @Ahmed-e4w1e 24 дня назад +1

    Thanks Alberto
    I ve a simple question, does using variables a good thing in terms of optimization and speed?
    Thanks

  • @mwaltercpa
    @mwaltercpa 5 месяцев назад +1

    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!

    • @SQLBI
      @SQLBI  5 месяцев назад +1

      It's a debug technique: www.sqlbi.com/articles/debugging-dax-measures-in-power-bi/

  • @thegreatlobu
    @thegreatlobu 5 месяцев назад +1

    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?

    • @rapacov
      @rapacov 5 месяцев назад

      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.

    • @fernandoguajardo6913
      @fernandoguajardo6913 5 месяцев назад

      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

    • @TRZMac
      @TRZMac 4 месяца назад +1

      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.

  • @nevermore17011990
    @nevermore17011990 5 месяцев назад

    newbie to DAX, and this video is very helpful. please do more, i'm watching all of your videos. Tks Sir !

  • @TRZMac
    @TRZMac 5 месяцев назад +1

    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!

  • @louism.4980
    @louism.4980 4 месяца назад +1

    This is gold, thank you so much Alberto!

  • @cathrerinezetadrones3169
    @cathrerinezetadrones3169 5 месяцев назад +1

    I will never forget these examples!

  • @jyrrin
    @jyrrin 4 месяца назад +1

    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!

    • @SQLBI
      @SQLBI  4 месяца назад +1

      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.

  • @NMC-el7to
    @NMC-el7to 2 месяца назад +1

    very nicely explained Sir, Thanks a lot....Sir,

  • @drkamikaze1
    @drkamikaze1 5 месяцев назад

    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

  • @dineshs9670
    @dineshs9670 5 месяцев назад

    The value of variables will be constant when used in calculated columns also ?
    I am using a column in my calculated column variable

  • @HebertAG
    @HebertAG 5 месяцев назад

    So for time intelligence like SAMEPERIODLASTYEAR or DATEADD we should use the measure always instead of the Variable?

  • @krisihari
    @krisihari 5 месяцев назад +1

    Very helpful video as always! Thanks!

  • @GaryThomann-CoGC
    @GaryThomann-CoGC 5 месяцев назад

    14:30 good point, variables (VAR) are constants

  • @kaselchirman9911
    @kaselchirman9911 5 месяцев назад

    This was very insightful, thank you Alberto

  • @kot23
    @kot23 5 месяцев назад

    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

    • @SQLBI
      @SQLBI  5 месяцев назад

      Yes.

  • @santoshkulkarni902
    @santoshkulkarni902 5 месяцев назад +1

    Very nicely explained ❤

  • @ngoduyvu
    @ngoduyvu 5 месяцев назад +1

    Thanks for a great video as always

  • @TopnutritionCl
    @TopnutritionCl 5 месяцев назад +1

    when i had that scenario i avoided the variables, because they gave me the wrong result. now i understand better how to use them

  • @kennethstephani692
    @kennethstephani692 5 месяцев назад +1

    Terrific video!

  • @manuelmontalvo2007
    @manuelmontalvo2007 24 дня назад +2

    👏👏👏👏👏👏👏👏

  • @timolff9239
    @timolff9239 5 месяцев назад

    Long awaited video for me

  • @mabl4367
    @mabl4367 5 месяцев назад

    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?

    • @SQLBI
      @SQLBI  5 месяцев назад +2

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

    • @mabl4367
      @mabl4367 5 месяцев назад +1

      @@SQLBI I understand.

  • @MrSparkefrostie
    @MrSparkefrostie 5 месяцев назад

    I am very interested in how and why you would use error in the real world

    • @SQLBI
      @SQLBI  5 месяцев назад

      An example is to stop a refresh using it in a calculated column or table when there is an issue in data.

    • @MrSparkefrostie
      @MrSparkefrostie 5 месяцев назад

      @@SQLBI awesome thank you, I will look into this, didn't even occur to me

    • @MrSparkefrostie
      @MrSparkefrostie 5 месяцев назад

      @@SQLBI seems my earlier comment got lost, but thank you, this is a brilliant idea

  • @marcofestu
    @marcofestu 5 месяцев назад

    The magister himself

  • @mogarrett3045
    @mogarrett3045 5 месяцев назад

    excellent

  • @vanlessing
    @vanlessing 5 месяцев назад +1

    VAR == constant and =|= variable