Switch syntax causing issues with loading visuals

[Vinod_V_12]

Hi,

Please could you help me to simply the below DAX. Our Dataset is connected to SAP HANA via direct query. The visual is not getting loaded as the query is consuming a lot of profile memory allocated in HANA. On the analysis, it was found that this switch syntax is causing the issue. 

Suppress Sales Switch =

SWITCH(

SELECTEDVALUE('Total Sales Slicer'[Total Sales Criteria]) ,

"Total Sales = 0", if([Suppress Sales] = 0, [Suppress Sales], BLANK()),

"Total Sales < 0", if([Suppress Sales] < 0, [Suppress Sales], BLANK()),

"Total Sales > 0", if([Suppress Sales)] > 0, [Suppress Sales], BLANK()),

"Total Sales <> 0", if([Suppress Sales] <> 0, [Suppress Sales)], BLANK()),

[Suppress Sales]

)

The above is used as a visual filter and the condition is set to "is not blank". The field Total Sales Criteria is then used in a slicer as the business wants to filter the data by total sales =0, >0,<0,<>0 and All

Suppress Sales = sumx('Calculated Measures',abs(COALESCE([Value Field 1],0)) + abs(COALESCE([Value Field 2],0))+ abs(COALESCE([Value Field 3],0)))

Total Sales Slicer Table:

Total Sales Criteria	Flag
Total Sales All	0
Total Sales <0	1
Total Sales = 0	2
Total Sales >0	3
Total Sales <>0	4

Any advise to replace Switch which is causing the issue?

[mahoneypat · Accepted solution]

That measure (and the one it references) might need more optimization but for starters, you can use a variable like this to reduce to amount of re-calculation.

Suppress Sales Switch =
VAR Suppress = [Suppress Sales]
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Total Sales Slicer'[Total Sales Criteria] ),
        "Total Sales = 0", IF ( Suppress = 0, Suppress, BLANK () ),
        "Total Sales < 0", IF ( Suppress < 0, Suppress, BLANK () ),
        "Total Sales > 0", IF ( Suppress > 0, Suppress, BLANK () ),
        "Total Sales <> 0", IF ( Suppress <> 0, Suppress, BLANK () ),
        Suppress
    )

Pat

[Vinod_V_12]

Thanks very much for the response Pat. This is brilliant.