Forum Discussion
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]
)
Hello Anonymous
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] )
2 Replies
Hello Anonymous
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] )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.
