locked
How Do I Use DAX Variables in Excel 2016 Power Pivot, VAR RETURN? RRS feed

  • Question

  • Dear Team,

    I have been hearing about the ability to use Variables in DAX in Excel 2016 for a while, but I just can't figure out how to do it. I have searched the internet and I can't find any example for Excel.

    With NO Variable this formula works:

    12MonthRollingAverage03:=

    IF (
            MAX ( dDate[Year] ) > 2016,
            CALCULATE (
                AVERAGE ( fSales[Sales] ),
                DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), -1, YEAR )
            )
        )

    With a Variable, this formula returns a blank in all cells in the PivotTable:

    12MonthRollingAverage02:=

    VAR MinYear = MIN ( dDate[Year] )
    RETURN
        IF (
            MAX ( dDate[Year] ) > MinYear,
            CALCULATE (
                AVERAGE ( fSales[Sales] ),
                DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), -1, YEAR )
            )
        )

    Is my syntax correct for using a variable in Excel? Or is something else wrong?

    Thank you in advance.

    Sincerely, Michael Girvin

    Tuesday, April 18, 2017 11:12 PM

Answers

  • Hi Mike,

    After research, your saytax is right. You'd better confirm if the result of DAX part aligns with your requirement. I thank you add the [Year] as field, and the [12MonthRollingAverage02] as measure, it return blank, right? If it is, because the DAX is affected by context. For each year,  MIN ( dDate[Year] ) and  MAX ( dDate[Year] ) return the same value, so the logical condition in If is false, so it return the blank. Please change the condition as  "MAX ( dDate[Year] ) >= MinYear", and check if it works fine.

    In addition, if you want to get the min year of all years, you should add a filter in MIN function, like the following formula. 

    12MonthRollingAverage02:=
    
    VAR MinYear = CALCULATE(MIN ( dDate[Year] ),ALL(dDate))
    RETURN
        IF (
            MAX ( dDate[Year] ) > MinYear,
            CALCULATE (
                AVERAGE ( fSales[Sales] ),
                DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), -1, YEAR )
            )
        )
    Please feel free to ask if you have other questions.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Wednesday, April 19, 2017 3:13 AM
  • Hi Mike,

    What you've understood so far is correct. The DAX variable will be evaluated within the original/first evaluation context. So, for a PivotTable, the context would be defined by the rows, columns, filters, and slicers. 

    Whatever the variable's expression would return as a stand-alone measure, for a given cell, is the same value it will return when it is evaluated as a variable.

    In your example scenario, this variable...

    VAR MinYear = MIN ( dDate[Year] )

    ...will return the same value as this measure...

    MinYear := MIN ( dDate[Year] )

    ...for a given cell.

    If you create the above measure and place it in your PivotTable, you will be looking at what the variable expression is, or would be, returning for each cell. It becomes even clearer why the following doesn't work as expected...

    12MonthRollingAverage02:=
    VAR MinYear = MIN ( dDate[Year] )
    RETURN
        IF (
            MAX ( dDate[Year] ) > MinYear,
            CALCULATE (
                AVERAGE ( fSales[Sales] ),
                DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), -1, YEAR )
            )
        )

    ...And why the variable based on what Angelia suggested, and what you previously attempted, does...

    12MonthRollingAverage02:=
    VAR MinYear = CALCULATE(MIN ( dDate[Year] ),ALL(dDate))
    RETURN
        IF (
            MAX ( dDate[Year] ) > MinYear,
            CALCULATE (
                AVERAGE ( fSales[Sales] ),
                DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), -1, YEAR )
            )
        )

    To summarise, in order to expand the evaluation context beyond the original one (defined by the rows, columns, filters, etc.), we still need to use the CALCULATE/CALCULATETABLE function arguments when defining variables.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    • Edited by Michael Amadi Thursday, April 20, 2017 9:56 AM Minor edit
    • Marked as answer by Mike GirvinMVP Thursday, April 20, 2017 11:55 PM
    Wednesday, April 19, 2017 11:59 PM

All replies

  • Hi Mike,

    After research, your saytax is right. You'd better confirm if the result of DAX part aligns with your requirement. I thank you add the [Year] as field, and the [12MonthRollingAverage02] as measure, it return blank, right? If it is, because the DAX is affected by context. For each year,  MIN ( dDate[Year] ) and  MAX ( dDate[Year] ) return the same value, so the logical condition in If is false, so it return the blank. Please change the condition as  "MAX ( dDate[Year] ) >= MinYear", and check if it works fine.

    In addition, if you want to get the min year of all years, you should add a filter in MIN function, like the following formula. 

    12MonthRollingAverage02:=
    
    VAR MinYear = CALCULATE(MIN ( dDate[Year] ),ALL(dDate))
    RETURN
        IF (
            MAX ( dDate[Year] ) > MinYear,
            CALCULATE (
                AVERAGE ( fSales[Sales] ),
                DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), -1, YEAR )
            )
        )
    Please feel free to ask if you have other questions.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Wednesday, April 19, 2017 3:13 AM
  • Hi Mike,

    do you get an error-message - if yes: What does it say?

    Or does it not return the expected result? Then: What do you expect to see and what is showing instead? 


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    Wednesday, April 19, 2017 5:53 AM
    Answerer
  • Thank you for answering, Angelia!

    I thought the main three reasons to use Variables were:

    1) Clarity of code

    2) Sometimes there is a performance benefit

    3) That the Variable Formula calculates in a context where it is defined instead of where it is used.

    In one of my earlier attempts at this formula, rather than the hard coded value 2016, I used what you suggested, CALCULATE(MIN ( dDate[Year] ),ALL(dDate)). But I was trying to replace it by using a variable that would calculate the value outside the PivotTable Context, where it was originally defined. 

    I am stunned to hear that the Variable can see the context in the PivotTable!?!?

    Now I am really confused : (


    Wednesday, April 19, 2017 6:24 PM
  • To answer your other two points:

    1) Year and Month are on the Row Area of the PivotTable. That is not the problem because both of these formulas work:

    12MonthRollingAverage03:=IF (
            MAX ( dDate[Year] ) > 2016,
            CALCULATE (
                AVERAGE ( fSales[Sales] ),
                DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), -1, YEAR )
            )
        )

    and

    12MonthRollingAverage03:=IF (
            MAX ( dDate[Year] ) > CALCULATE(MIN(dDate[Year]),ALL(dDate[Year])),
            CALCULATE (
                AVERAGE ( fSales[Sales] ),
                DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), -1, YEAR )
            )
        )

    2) This was not what I wanted: MAX ( dDate[Year] ) >= MinYear, becasue I wanted to exclude only the first year in the data set.

    Thank you again for trying to help me, 
    Angelia.

    Sincerely, Michael Girvin

    Wednesday, April 19, 2017 6:29 PM
  • Thank you for your response. I did not get an error, just a blank result.
    Wednesday, April 19, 2017 6:30 PM
  • Hi Mike,

    What you've understood so far is correct. The DAX variable will be evaluated within the original/first evaluation context. So, for a PivotTable, the context would be defined by the rows, columns, filters, and slicers. 

    Whatever the variable's expression would return as a stand-alone measure, for a given cell, is the same value it will return when it is evaluated as a variable.

    In your example scenario, this variable...

    VAR MinYear = MIN ( dDate[Year] )

    ...will return the same value as this measure...

    MinYear := MIN ( dDate[Year] )

    ...for a given cell.

    If you create the above measure and place it in your PivotTable, you will be looking at what the variable expression is, or would be, returning for each cell. It becomes even clearer why the following doesn't work as expected...

    12MonthRollingAverage02:=
    VAR MinYear = MIN ( dDate[Year] )
    RETURN
        IF (
            MAX ( dDate[Year] ) > MinYear,
            CALCULATE (
                AVERAGE ( fSales[Sales] ),
                DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), -1, YEAR )
            )
        )

    ...And why the variable based on what Angelia suggested, and what you previously attempted, does...

    12MonthRollingAverage02:=
    VAR MinYear = CALCULATE(MIN ( dDate[Year] ),ALL(dDate))
    RETURN
        IF (
            MAX ( dDate[Year] ) > MinYear,
            CALCULATE (
                AVERAGE ( fSales[Sales] ),
                DATESINPERIOD ( dDate[Date], LASTDATE ( dDate[Date] ), -1, YEAR )
            )
        )

    To summarise, in order to expand the evaluation context beyond the original one (defined by the rows, columns, filters, etc.), we still need to use the CALCULATE/CALCULATETABLE function arguments when defining variables.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    • Edited by Michael Amadi Thursday, April 20, 2017 9:56 AM Minor edit
    • Marked as answer by Mike GirvinMVP Thursday, April 20, 2017 11:55 PM
    Wednesday, April 19, 2017 11:59 PM
  • I'm very impressed by Michael's description. It makes sense to me, because I'm aware of the "elephant in the room" here, called "Context transition".

    In case you're not familiar with it, this might be a good start: https://www.sqlbi.com/articles/understanding-context-transition/

    The use of variables makes a big difference in syntax once used where context transition takes place (so within a CALCULATE-statement for example). But within the example given here, we see actually not much difference.


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    Thursday, April 20, 2017 9:20 AM
    Answerer
  • Thank you for taking the time to answer my questions, Michael Amadi! I was mistakenly assuming that original context was the Measure grid where I created the formula. From what you say, now I see that each cell in Pivot is the original context.

    Thursday, April 20, 2017 11:54 PM