Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I'm getting incorrect values after trying an optimisation, and I cant understand why, or if my DAX can actually be optimised. I'm using DAX Optimiser for the first time and one of it's suggestions is to see if I can move a calculation outside of in iteration by placing it in a variable.
My orginal DAX is:
VAR CurrentInterval =
SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_END_DATE] )
VAR StartDate =
SELECTEDVALUE( 'RLS CALENDAR'[YEAR_START_DATE] )
VAR Grain =
SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_GRAIN] )
VAR Selected_Territory =
SELECTEDVALUE ( 'RLS CALENDAR'[TERRITORY_CODE] )
RETURN
SUMX (
FILTER (
ALL ( 'RLS CALENDAR' ),
'RLS CALENDAR'[LEVEL_1_END_DATE] > StartDate
&& 'RLS CALENDAR'[LEVEL_1_END_DATE] <= CurrentInterval
&& 'RLS CALENDAR'[LEVEL_1_GRAIN] = Grain
&& 'RLS CALENDAR'[TERRITORY_CODE] = Selected_Territory
),
[Total volume]
)
And I have attempted to optimise it by creating a variable for the total volume measure:
VAR CurrentInterval =
SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_END_DATE] )
VAR StartDate =
SELECTEDVALUE( 'RLS CALENDAR'[YEAR_START_DATE] )
VAR Grain =
SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_GRAIN] )
VAR Selected_Territory =
SELECTEDVALUE ( 'RLS CALENDAR'[TERRITORY_CODE] )
VAR calculate_volume =
IF (
ISFILTERED ( 'filter_list'[filter_name] ),
CALCULATE (
SUM ( 'MARKET'[TOTAL_QTY] ),
USERELATIONSHIP ( 'filter_list'[ITEM], 'PRODUCT_ATTRIBUTES'[ITEM] )
),
VAR calc =
SUM ( 'MARKET'[TOTAL_QTY] )
VAR blank_check =
IF ( NOT ( ISBLANK ( calc ) ), calc )
RETURN
blank_check
)
RETURN
SUMX (
FILTER (
ALL ( 'RLS CALENDAR' ),
'RLS CALENDAR'[LEVEL_1_END_DATE] > StartDate
&& 'RLS CALENDAR'[LEVEL_1_END_DATE] <= CurrentInterval
&& 'RLS CALENDAR'[LEVEL_1_GRAIN] = Grain
&& 'RLS CALENDAR'[TERRITORY_CODE] = Selected_Territory
),
calculate_volume
)
This makes the measure much faster (using DAX Studio), but the results are now incorrect. I understand this is likely to do with the context transition and the fact the final query now takes place over the 50,000 rows of the calendar, rather than the 13,000,000 rows of Market.
However can anyone explain this to me in full, and make a suggestion for an optimision that would work?
(I need to use the whole table inside the ALL() rather than selected columns from the celendar, and in any case no benifit was observed)
Solved! Go to Solution.
Can you try the following :
VAR CurrentInterval =
SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_END_DATE] )
VAR StartDate =
SELECTEDVALUE( 'RLS CALENDAR'[YEAR_START_DATE] )
VAR Grain =
SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_GRAIN] )
VAR Selected_Territory =
SELECTEDVALUE ( 'RLS CALENDAR'[TERRITORY_CODE] )
VAR calculate_volume =
CALCULATE (
[Total volume],
FILTER (
ALL ( 'RLS CALENDAR' ),
'RLS CALENDAR'[LEVEL_1_END_DATE] > StartDate
&& 'RLS CALENDAR'[LEVEL_1_END_DATE] <= CurrentInterval
&& 'RLS CALENDAR'[LEVEL_1_GRAIN] = Grain
&& 'RLS CALENDAR'[TERRITORY_CODE] = Selected_Territory
)
)
RETURN
calculate_volume
===============OR===================
VAR CurrentInterval =
SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_END_DATE] )
VAR StartDate =
SELECTEDVALUE( 'RLS CALENDAR'[YEAR_START_DATE] )
VAR Grain =
SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_GRAIN] )
VAR Selected_Territory =
SELECTEDVALUE ( 'RLS CALENDAR'[TERRITORY_CODE] )
RETURN
SUMX (
FILTER (
ALL ( 'RLS CALENDAR' ),
'RLS CALENDAR'[LEVEL_1_END_DATE] > StartDate
&& 'RLS CALENDAR'[LEVEL_1_END_DATE] <= CurrentInterval
&& 'RLS CALENDAR'[LEVEL_1_GRAIN] = Grain
&& 'RLS CALENDAR'[TERRITORY_CODE] = Selected_Territory
),
[Total volume]
)
The above two expressions calculate the Total volume measure outside of the iteration and place it in a variable. These should help improve performance by reducing the number of iterations required to calculate the expression.
You can read Marco Russo's article on optimizing DAX expressions:
https://www.sqlbi.com/articles/optimizing-dax-expressions-involving-multiple-measures/
Please let me know if this help.
Thanks
Babatunde Dallas
It is good to know the 1st expression makes a positive impact. You can use a combination of SUMMARIZE and SUMX to perform the aggregation.
This approach will still adapt to the filter context while improving performance.
You can try :
VAR CurrentInterval = SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_END_DATE] )
VAR StartDate = SELECTEDVALUE( 'RLS CALENDAR'[YEAR_START_DATE] )
VAR Grain = SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_GRAIN] )
VAR Selected_Territory = SELECTEDVALUE ( 'RLS CALENDAR'[TERRITORY_CODE] )
VAR FilteredCalendar =
FILTER (
ALL ( 'RLS CALENDAR' ),
'RLS CALENDAR'[LEVEL_1_END_DATE] > StartDate
&& 'RLS CALENDAR'[LEVEL_1_END_DATE] <= CurrentInterval
&& 'RLS CALENDAR'[LEVEL_1_GRAIN] = Grain
&& 'RLS CALENDAR'[TERRITORY_CODE] = Selected_Territory
)
RETURN
SUMX (
SUMMARIZE (
FilteredCalendar,
'RLS CALENDAR'[LEVEL_1_END_DATE], -- Group by the relevant columns
'RLS CALENDAR'[LEVEL_1_GRAIN],
'RLS CALENDAR'[TERRITORY_CODE]
),
CALCULATE(
SUM ( 'MARKET'[TOTAL_QTY] ),
USERELATIONSHIP ( 'filter_list'[ITEM], 'PRODUCT_ATTRIBUTES'[ITEM] )
)
)
============= OR use SUMX to iterate over the filtered table and calculate the sum of the “Total volume” column. Which can also help improve performance using CALCULATE function
VAR CurrentInterval = SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_END_DATE] )
VAR StartDate = SELECTEDVALUE( 'RLS CALENDAR'[YEAR_START_DATE] )
VAR Grain = SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_GRAIN] )
VAR Selected_Territory = SELECTEDVALUE ( 'RLS CALENDAR'[TERRITORY_CODE] )
RETURN
SUMX (
FILTER (
ALL ( 'RLS CALENDAR' ),
'RLS CALENDAR'[LEVEL_1_END_DATE] > StartDate
&& 'RLS CALENDAR'[LEVEL_1_END_DATE] <= CurrentInterval
&& 'RLS CALENDAR'[LEVEL_1_GRAIN] = Grain
&& 'RLS CALENDAR'[TERRITORY_CODE] = Selected_Territory
),
CALCULATE (
SUM ( 'MARKET'[TOTAL_QTY] )
)
)
============= OR
VAR CurrentInterval = SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_END_DATE] )
VAR StartDate = SELECTEDVALUE( 'RLS CALENDAR'[YEAR_START_DATE] )
VAR Grain = SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_GRAIN] )
VAR Selected_Territory = SELECTEDVALUE ( 'RLS CALENDAR'[TERRITORY_CODE] )
RETURN
SUMX (
FILTER (
ALL ( 'RLS CALENDAR' ),
'RLS CALENDAR'[LEVEL_1_END_DATE] > StartDate
&& 'RLS CALENDAR'[LEVEL_1_END_DATE] <= CurrentInterval
&& 'RLS CALENDAR'[LEVEL_1_GRAIN] = Grain
&& 'RLS CALENDAR'[TERRITORY_CODE] = Selected_Territory
),
CALCULATE(
SUM ( 'MARKET'[TOTAL_QTY] ),
USERELATIONSHIP ( 'filter_list'[ITEM], 'PRODUCT_ATTRIBUTES'[ITEM] )
)
)
I look forward to your response.
@DallasBaba - thank you for the further attempts, I too had been looking at a way to filter the Calendar in a variable with SUMMARIZE and then pass this to the SUMX, however whether I calculate the measure in a variable, or if I reference the original measure inside the iteration, it always produces an incorrect result, and I have tried many different options outside of your suggestions. Looking at the queries in DAX studio, this is because the iteration happens over the Calendar, rather than the FACT, or any other dimension in the visual.
Using CALCULATE, the calculation is run over the dimension from the visual, taking into account the filters from the CALENDAR, which is both what I need and an optimisation on the original SUMX with the referenced measure, which iterated over the FACT table.
Thank you for your assistance, I will proceed with CALCULATE and give up on teaching myself the NOCALCULATE method for now!
@DallasBaba - Thanks for this, your 2nd measure does not appear to be any different than my first one, perhaps you copied the wrong DAX?
Your first measure works, but I should have mentioned I was aware I could do this but am attempting to avoid CALCULATE. I have accepted this as a solution though as technically you have optimised my measure, and I dont want people to think it's not better than:
VAR CurrentInterval =
SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_END_DATE] )
VAR StartDate =
SELECTEDVALUE ( 'RLS CALENDAR'[YEAR_START_DATE] )
VAR Grain =
SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_GRAIN] )
VAR Selected_Territory =
SELECTEDVALUE ( 'RLS CALENDAR'[TERRITORY_CODE] )
RETURN
SUMX (
FILTER (
ALL ( 'RLS CALENDAR' ),
'RLS CALENDAR'[LEVEL_1_END_DATE] > StartDate
&& 'RLS CALENDAR'[LEVEL_1_END_DATE] <= CurrentInterval
&& 'RLS CALENDAR'[LEVEL_1_GRAIN] = Grain
&& 'RLS CALENDAR'[TERRITORY_CODE] = Selected_Territory
),
[Total volume]
)
If you had intended to provide an optimal version with SUMX could you do so?
Can you try the following :
VAR CurrentInterval =
SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_END_DATE] )
VAR StartDate =
SELECTEDVALUE( 'RLS CALENDAR'[YEAR_START_DATE] )
VAR Grain =
SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_GRAIN] )
VAR Selected_Territory =
SELECTEDVALUE ( 'RLS CALENDAR'[TERRITORY_CODE] )
VAR calculate_volume =
CALCULATE (
[Total volume],
FILTER (
ALL ( 'RLS CALENDAR' ),
'RLS CALENDAR'[LEVEL_1_END_DATE] > StartDate
&& 'RLS CALENDAR'[LEVEL_1_END_DATE] <= CurrentInterval
&& 'RLS CALENDAR'[LEVEL_1_GRAIN] = Grain
&& 'RLS CALENDAR'[TERRITORY_CODE] = Selected_Territory
)
)
RETURN
calculate_volume
===============OR===================
VAR CurrentInterval =
SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_END_DATE] )
VAR StartDate =
SELECTEDVALUE( 'RLS CALENDAR'[YEAR_START_DATE] )
VAR Grain =
SELECTEDVALUE ( 'RLS CALENDAR'[LEVEL_1_GRAIN] )
VAR Selected_Territory =
SELECTEDVALUE ( 'RLS CALENDAR'[TERRITORY_CODE] )
RETURN
SUMX (
FILTER (
ALL ( 'RLS CALENDAR' ),
'RLS CALENDAR'[LEVEL_1_END_DATE] > StartDate
&& 'RLS CALENDAR'[LEVEL_1_END_DATE] <= CurrentInterval
&& 'RLS CALENDAR'[LEVEL_1_GRAIN] = Grain
&& 'RLS CALENDAR'[TERRITORY_CODE] = Selected_Territory
),
[Total volume]
)
The above two expressions calculate the Total volume measure outside of the iteration and place it in a variable. These should help improve performance by reducing the number of iterations required to calculate the expression.
You can read Marco Russo's article on optimizing DAX expressions:
https://www.sqlbi.com/articles/optimizing-dax-expressions-involving-multiple-measures/
Please let me know if this help.
Thanks
Babatunde Dallas
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
10 | |
6 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |