Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
@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
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@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
I have some DAX Performance Tuning articles here:
These are also good articles:
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 ?
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |