Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
zaza
Resolver III
Resolver III

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 5
Anonymous
Not applicable

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.
CNENFRNL
Community Champion
Community Champion

@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!

amitchandak
Super User
Super User

@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

Greg_Deckler
Super User
Super User

@zaza 

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?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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] )

Screen Shot 2020-09-15 at 12.45.58 PM.png

Can I do this somehow without using SUMMARIZE ?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors