cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hymieho2 Frequent Visitor
Frequent Visitor

DAX optimization & variables with SWITCH statement

If I have a "master" measure that will return a different calculation based on a disconnected slicer, will the variables be calculated only if needed or will they all be calculated before the RETURN section is evaluated?

 

For example in this measure, should I remove the variables and put the associated DAX directly in the SWITCH statement to ensure I am not calculating all possible output measures before the RETURN section determines which cacluation I actually need, or is PBI smart enough to know which variables are needed by the SWITCH and only calculate what is needed?

 

Time_Calculation_Total_Labor_Dollars = 
VAR filter_week =
    MAX ( dimSalesDates[Week_End_Date] )
VAR prior_year =
    CALCULATE (
        [Total_Labor_Dollars],
        FILTER (
            ALLEXCEPT (
                dimSalesDates,
                dimSalesDates[Sales_Week_Number],
                dimSalesDates[Period_Number]
            ),
            dimSalesDates[Year]
                = MAX ( dimSalesDates[Year] ) - 1
        )
    )
VAR prior_year_growth = [Total_Labor_Dollars] - prior_year
VAR prior_year_growth_percent =
    FORMAT ( DIVIDE ( prior_year_growth, prior_year ), "Percent" )
VAR prior_year_to_date =
    CALCULATE (
        [Total_Labor_Dollars],
        ALL ( dimSalesDates ),
        dimSalesDates[Week_End_Date] <= filter_week - 364
    )
VAR year_to_date =
    CALCULATE (
        [Total_Labor_Dollars],
        ALLEXCEPT ( dimSalesDates, dimSalesDates[Year] ),
        dimSalesDates[Week_End_Date] <= filter_week
    )
VAR ytd_growth = year_to_date - prior_year_to_date
VAR ytd_growth_percent =
    FORMAT ( DIVIDE ( ytd_growth, prior_year_to_date ), "Percent" )
RETURN
    IF (
        HASONEVALUE ( filterTimeCalculations[time_calculation] ),
        SWITCH (
            VALUES ( filterTimeCalculations[time_calculation] ),
            "Current Year", [Total_Labor_Dollars],
            "Prior Year", prior_year,
            "Prior Year Growth", prior_year_growth,
            "Prior Year Growth Percent", prior_year_growth_percent,
            "Prior Year YTD", prior_year_to_date,
            "Current YTD", year_to_date,
            "YTD Growth", ytd_growth,
            "YTD Growth %", ytd_growth_percent
        ),
            [Total_Labor_Dollars]
    )

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: DAX optimization & variables with SWITCH statement

Hello @hymieho2 

I believe each Variable will calc before it makes it to the switch but what you can do is turn each variable into a measure then just read your filterTimeCalculations[time_calculation] and do the switch.  This also makes it easier to add additional measures to your list and the measures you do write can be used in other parts of the report.

 

prior_year = 
    CALCULATE (
        [Total_Labor_Dollars],
        FILTER (
            ALLEXCEPT (
                dimSalesDates,
                dimSalesDates[Sales_Week_Number],
                dimSalesDates[Period_Number]
            ),
            dimSalesDates[Year]
                = MAX ( dimSalesDates[Year] ) - 1
        )
    )
prior_year_growth = [Total_Labor_Dollars] - [prior_year]
prior_year_growth_percent = 
    FORMAT ( DIVIDE ( [prior_year_growth], [prior_year] ), "Percent" )
prior_year_to_date = 
VAR filter_week =
    MAX ( dimSalesDates[Week_End_Date] )
RETURN
    CALCULATE (
        [Total_Labor_Dollars],
        ALL ( dimSalesDates ),
        dimSalesDates[Week_End_Date] <= filter_week - 364)
year_to_date = 
VAR filter_week =
    MAX ( dimSalesDates[Week_End_Date] )
RETURN
    CALCULATE (
        [Total_Labor_Dollars],
        ALLEXCEPT ( dimSalesDates, dimSalesDates[Year] ),
        dimSalesDates[Week_End_Date] <= filter_week
    )
ytd_growth = [year_to_date] - [prior_year_to_date]
ytd_growth_percent = 
    FORMAT ( DIVIDE ( [ytd_growth], [prior_year_to_date] ), "Percent" )
Time_Calculation_Total_Labor_Dollars_2 = 
VAR TimeCalcSelection = 
    SELECTEDVALUE( filterTimeCalculations[time_calculation], "Current Year")

RETURN
    SWITCH (
        TRUE(),
        TimeCalcSelection = "Current Year", [Total_Labor_Dollars],
        TimeCalcSelection = "Prior Year", [prior_year],
        TimeCalcSelection = "Prior Year Growth", [prior_year_growth],
        TimeCalcSelection = "Prior Year Growth Percent", [prior_year_growth_percent],
        TimeCalcSelection = "Prior Year YTD", [prior_year_to_date],
        TimeCalcSelection = "Current YTD", [year_to_date],
        TimeCalcSelection = "YTD Growth", [ytd_growth],
        TimeCalcSelection = "YTD Growth %", [ytd_growth_percent],
        [Total_Labor_Dollars]
    )

  

View solution in original post

2 REPLIES 2
Highlighted
Super User
Super User

Re: DAX optimization & variables with SWITCH statement

Hello @hymieho2 

I believe each Variable will calc before it makes it to the switch but what you can do is turn each variable into a measure then just read your filterTimeCalculations[time_calculation] and do the switch.  This also makes it easier to add additional measures to your list and the measures you do write can be used in other parts of the report.

 

prior_year = 
    CALCULATE (
        [Total_Labor_Dollars],
        FILTER (
            ALLEXCEPT (
                dimSalesDates,
                dimSalesDates[Sales_Week_Number],
                dimSalesDates[Period_Number]
            ),
            dimSalesDates[Year]
                = MAX ( dimSalesDates[Year] ) - 1
        )
    )
prior_year_growth = [Total_Labor_Dollars] - [prior_year]
prior_year_growth_percent = 
    FORMAT ( DIVIDE ( [prior_year_growth], [prior_year] ), "Percent" )
prior_year_to_date = 
VAR filter_week =
    MAX ( dimSalesDates[Week_End_Date] )
RETURN
    CALCULATE (
        [Total_Labor_Dollars],
        ALL ( dimSalesDates ),
        dimSalesDates[Week_End_Date] <= filter_week - 364)
year_to_date = 
VAR filter_week =
    MAX ( dimSalesDates[Week_End_Date] )
RETURN
    CALCULATE (
        [Total_Labor_Dollars],
        ALLEXCEPT ( dimSalesDates, dimSalesDates[Year] ),
        dimSalesDates[Week_End_Date] <= filter_week
    )
ytd_growth = [year_to_date] - [prior_year_to_date]
ytd_growth_percent = 
    FORMAT ( DIVIDE ( [ytd_growth], [prior_year_to_date] ), "Percent" )
Time_Calculation_Total_Labor_Dollars_2 = 
VAR TimeCalcSelection = 
    SELECTEDVALUE( filterTimeCalculations[time_calculation], "Current Year")

RETURN
    SWITCH (
        TRUE(),
        TimeCalcSelection = "Current Year", [Total_Labor_Dollars],
        TimeCalcSelection = "Prior Year", [prior_year],
        TimeCalcSelection = "Prior Year Growth", [prior_year_growth],
        TimeCalcSelection = "Prior Year Growth Percent", [prior_year_growth_percent],
        TimeCalcSelection = "Prior Year YTD", [prior_year_to_date],
        TimeCalcSelection = "Current YTD", [year_to_date],
        TimeCalcSelection = "YTD Growth", [ytd_growth],
        TimeCalcSelection = "YTD Growth %", [ytd_growth_percent],
        [Total_Labor_Dollars]
    )

  

View solution in original post

hymieho3 Frequent Visitor
Frequent Visitor

Re: DAX optimization & variables with SWITCH statement

Turns out that is not true: in the Definitive Guide to DAX there is a section on variables and it says DAX uses "lazy execution" meaning that variables are only evaluated when they are called.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,006)