Forum Discussion

Vinod_V_12's avatar
Vinod_V_12
Frequent Visitor
5 years ago
Solved

Switch syntax causing issues with loading visuals

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 all...
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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

     

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

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's avatar
    Vinod_V_12
    Frequent Visitor
    5 years ago

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