Sumx take so long time to load

[betty_bui2602]

Hello guys, 
Im working with big data set.
I have Running Total measure which calcualte: total supply - total Demand - Remaining Fcst

I need to calculate DC Suppportable

My measure:  

SOH (Donot use) = sumx(VALUES('Market Master'[Plant]),

if([SOH projected] < 0, [SOH projected] + [Remaining Fcst to Order], [Remaining Fcst to Order]))

DC Suportable qty = sumx(VALUES('Market Master'[Plant]),

if([SOH (Donot use)] >= 0 && NOT ISBLANK([Remaining Fcst to Order]), [SOH (Donot use)], [Total DC Supply] - [Total DC Demand]))

DC Risk = sumx(VALUES('Market Master'[Plant]),

if([DC Suportable qty] > [Remaining Fcst to Order], 0, ABS([DC Suportable qty] - [Remaining Fcst to Order])))

All these measure working find when i only select 1 Product ID. 
However, when remove filter on 1 product ID it takes forever to load 😢 
Can you guys tell me what happens? any suggest alternate measure? 

Thank you 

[ppm1]

The slowness is caused because the storage engine can't perform the IF evaluation used in your SUMX. One small thing you can do to speed it up is to use a variable.

SOH (Donot use) =
SUMX (
    VALUES ( 'Market Master'[Plant] ),
    VAR ThisSOH = [SOH projected]
    VAR ThisRemFcst = [Remaining Fcst to Order]
    RETURN
        IF ( ThisSOH < 0, ThisSOH + ThisRemFcst, ThisRemFcst )
)

However, to get a big improvement, you'll need to change the logic. What is your [SOH projected] measure? How many values of Plant are there in your model?

Pat

[betty_bui2602]

Thank for you recomment Pat. 
SOH projected: i tried to get SOH projected by end of each month 

SOH projected =

CALCULATE(

    [Total DC Supply] - [Total DC Demand] - [Remaining Fcst to Order],

    FILTER(

        ALLSELECTED('Calendar'[Date]),

        ISONORAFTER('Calendar'[Date], MAX('Merge Table'[Date]), DESC)

    )

)
What i need to archvie is supportable stock based on projected SOH at Product level. 

SOH (do not use) is just workaround solution. I cant find solution to jump direct to  DC support qty after SOH projected 😞