topic Switch syntax causing issues with loading visuals in DAX Commands and Tips

Switch syntax causing issues with loading visuals

Vinod_V_12 — Tue, 03 Aug 2021 20:07:37 GMT

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?

Re: Switch syntax causing issues with loading visuals

mahoneypat — Tue, 03 Aug 2021 21:25:10 GMT

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

Re: Switch syntax causing issues with loading visuals

Vinod_V_12 — Thu, 05 Aug 2021 19:14:21 GMT

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