Forum Discussion
Optimizing DAX Measure
I have the following measure whihc works perfectly. However my dataset grew from 2 million rows to 42 million rows and Now this measure takes way too much to load, sometimes some visuals where this is used multiple times even run out of memory.
ML Accuracy % =
VAR __SKUPlantCPG =
SUMMARIZE (
fact_forecast,
[Period],
[Lead SKU ID],
[Plant ID],
[CPG ID],
"__prediction", SUM ( fact_forecast[Prediction] ),
"__biasAbsolute", ABS ( SUM ( fact_forecast[ML Bias] ) )
)
VAR __BrandCPG =
SUMMARIZE (
fact_forecast,
[Period],
[Brand],
[CPG ID],
"__prediction", SUM ( fact_forecast[Prediction] ),
"__biasAbsolute", ABS ( SUM ( fact_forecast[ML Bias] ) )
)
VAR __valueSKUPlantCPG = SUMX ( __SKUPlantCPG, [__biasAbsolute] ) / SUMX ( __SKUPlantCPG, [__prediction] )
VAR __valueBrandCPG = SUMX ( __BrandCPG, [__biasAbsolute] ) / SUMX ( __BrandCPG, [__prediction] )
VAR __value =
IF ( SELECTEDVALUE ( 'disc_forecast_group'[Forecast Group] ) = "SKU-Plant-CPG", __valueSKUPlantCPG,
IF ( SELECTEDVALUE ( 'disc_forecast_group'[Forecast Group] ) = "Brand-CPG", __valueBrandCPG,
ABS ( SUM ( fact_forecast[ML Bias] ) ) / SUM ( fact_forecast[Prediction] )
) )
VAR result =
IF ( ISBLANK ( __value ), BLANK(), 1 - __value )
RETURN result
This needs to be a measure, I can't use calculated table and I tried to do marcorusso 's recommendation on optimizing the SUMMARIZE function but that didn't work for me because the calculation results will be different than what is expected.
How can I further optimize this measure?
5 Replies
I have some DAX Performance Tuning articles here:
- https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275
- https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-2/ba-p/976813
These are also good articles:
- https://maqsoftware.com/expertise/powerbi/dax-best-practices
- https://www.sqlgene.com/2019/09/27/a-comprehensive-guide-to-power-bi-performance-tuning/
- Video: https://www.sqlbi.com/tv/optimizing-analyzing-dax-query-plans-sqlbits-xii/#
- https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization
Sorry for the link spam but it's a broad topic and the information you have provided is limited. Will try to take a closer look at the formula. Can you provide PBIX file or sample source data?
CNENFRNL This would not work as the ABS calculation will not return the correct values. I did try to see and the performance is just slightly better. I realize the issue is with the SUMMARIZE function and I need to do this without using SUMMARIZE.
Greg_Deckler I read trough the links you provided and I am already doing everything as optimally as possible. I abstracted out the main calculation that is causing the issue into a simple example: PowerBI Absolute Value.pbix
The calculated measure returns the same result as the ABS function except for the totals. As you can see in the totals the absolute values for each category is summed up.
Category Absolute = VAR __table = SUMMARIZE ( 'Table', 'Table'[Category], "Absolute", ABS ( SUM ( 'Table'[Value] ) ) ) RETURN SUMX ( __table, [Absolute] )Can I do this somehow without using SUMMARIZE ?
zaza , looking at your formula, there are only two things I can think of as of now.
1. if , You can get the second table from the first Table.
2. On the First table, use addcolumns and add the other two columns using all except
zaza I'd tweak the measure this way; pls tell me the practical effect after you give it a shot.
Sum Prediction = SUM ( fact_forecast[Prediction] ) Abs Sum Bias = ABS ( SUM ( fact_forecast[ML Bias] ) ) ML Accuracy % = VAR __SKUPlantCPG = SUMMARIZE ( fact_forecast, [Period], [Lead SKU ID], [Plant ID], [CPG ID] ) VAR __BrandCPG = SUMMARIZE ( fact_forecast, [Period], [Brand], [CPG ID] ) VAR __valueSKUPlantCPG = SUMX ( __SKUPlantCPG, [Abs Sum Bias] ) / SUMX ( __SKUPlantCPG, [Sum Prediction] ) VAR __valueBrandCPG = SUMX ( __BrandCPG, [Abs Sum Bias] ) / SUMX ( __BrandCPG, [Sum Prediction] ) VAR __value = IF ( SELECTEDVALUE ( 'disc_forecast_group'[Forecast Group] ) = "SKU-Plant-CPG", __valueSKUPlantCPG, IF ( SELECTEDVALUE ( 'disc_forecast_group'[Forecast Group] ) = "Brand-CPG", __valueBrandCPG, [Abs Sum Bias] / [Sum Prediction] ) ) VAR result = IF ( ISBLANK ( __value ), BLANK (), 1 - __value ) RETURN result- The advice is this: if your fact table is big, then instead of SUMMARIZE (that has to scan the fact table) try to use CROSSJOIN. If the number of combinations you have under your SUMMARIZE is decent (relatively low), this should give you a faster measure. But it all depends on your data distribution. CROSSJOIN does not have to scan the whole fact table, only the relevant (small) dimensions.
