Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] )
Solved! Go to Solution.
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] )
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |