Forum Discussion

zaza's avatar
zaza
Resolver III
5 years ago

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 i...
CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

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