if-statementpowerbidax

Power BI DAX - How Can I use Case Switch instead of IFs


I would like to know how can I change this DAX Code, to use Case Switch instead of IFs

IF(VALUES(Rankings\[Attribute\]) = "AERIAL DUELS WON %", format(SUM(Rankings\[Value\]),"##%"),
IF(VALUES(Rankings\[Attribute\]) = "CROSSES",FORMAT(SUM(Rankings\[Value\]),"##"),
IF(VALUES(Rankings\[Attribute\]) = "Crossing%",FORMAT(SUM(Rankings\[Value\]),"##%"),
IF(VALUES(Rankings\[Attribute\]) = "FORWARD PASSES %",FORMAT(SUM(Rankings\[Value\]),"##%"),
IF(VALUES(Rankings\[Attribute\]) = "FOULS COMMITTED",FORMAT(SUM(Rankings\[Value\]),"##"),
IF(VALUES(Rankings\[Attribute\]) = "GOALS",FORMAT(SUM(Rankings\[Value\]),"##"),
IF(VALUES(Rankings\[Attribute\]) = "GOALS CONCEDED",FORMAT(SUM(Rankings\[Value\]),"##"),
IF(VALUES(Rankings\[Attribute\]) = "GROUND DUELS WON %",FORMAT(SUM(Rankings\[Value\]),"##%"),
IF(VALUES(Rankings\[Attribute\]) = "NON-PENALTY SHOT CONVERSION RATE %",FORMAT(SUM(Rankings\[Value\]),"##%"),
IF(VALUES(Rankings\[Attribute\]) = "NON-PENALTY SHOTS",FORMAT(SUM(Rankings\[Value\]),"##"),
IF(VALUES(Rankings\[Attribute\]) = "OWN HALF BALL LOSS",FORMAT(SUM(Rankings\[Value\]),"##"),
IF(VALUES(Rankings\[Attribute\]) = "PASS FORWARD IN FINAL 3RD %",FORMAT(SUM(Rankings\[Value\]),"##%"),
IF(VALUES(Rankings\[Attribute\]) = "PASSES",FORMAT(SUM(Rankings\[Value\]),"##"),
IF(VALUES(Rankings\[Attribute\]) = "POSSESSION REGAINS IN OPPO HALF TOTAL",FORMAT(SUM(Rankings\[Value\]),"##"),
IF(VALUES(Rankings\[Attribute\]) = "Possession%",FORMAT(SUM(Rankings\[Value\]),"##%"),
IF(VALUES(Rankings\[Attribute\]) = "RED CARDS",IF(SUM(Rankings\[Value\]) \>0, FORMAT(SUM(Rankings\[Value\]),"##"),0),
IF(VALUES(Rankings\[Attribute\]) = "SHOTS INSIDE BOX",FORMAT(SUM(Rankings\[Value\]),"##"),
IF(VALUES(Rankings\[Attribute\]) = "SHOTS ON TARGET %",FORMAT(SUM(Rankings\[Value\]),"##%"),
IF(VALUES(Rankings\[Attribute\]) = "SHOTS OUTSIDE BOX",FORMAT(SUM(Rankings\[Value\]),"##"),
IF(VALUES(Rankings\[Attribute\]) = "Successful Box Cross",FORMAT(SUM(Rankings\[Value\]),"##"),
IF(VALUES(Rankings\[Attribute\]) = "YELLOW CARDS",FORMAT(SUM(Rankings\[Value\]),"##")
)))))))))))))))))))))`

I need to change the format to use CASE SWITCH


Solution

  • While the other answers work, SWITCH uses the initial value as the comparison, so you don't have to repeat it for each value:

    SWITCH(VALUES(Rankings[Attribute])
            , "AERIAL DUELS WON %", format(SUM(Rankings[Value]),"##%")
            , "CROSSES",FORMAT(SUM(Rankings[Value]),"##")
            , ...
            )
    

    The TRUE is usually used when the expressions use different fields.

    See MS Docs - SWITCH for more details.