DAX Support - Switch statement - Colouring for conditional formatting

[JoeVincent]

Hi all!

I have hit a stumbling block in my learning. I am really struggling with the DAX coding. I am looking to colour multiple values based on specific row categories. I can manage to do it for 1 category but then when I try to add more into the mix, it all falls apart.

Below is the DAX code I can get working for colouring values in my matrix table which are categorised as 'Chargeable' as the colour of light green:

Background color = IF(MAX('TL Forecast Bookings'[Booking Type]) = "Chargeable", "LightGreen", BLANK()))

This is the SWITCH code that I can't get to work where I am looking to colour multiple different booking types to different colours:

Colour by category =

SWITCH (

TRUE (),

MAX('TL Forecast Bookings'[Booking Type] = "Chargeable", "LightGreen")

MAX('TL Forecast Bookings'[Booking Type] = "Provisional/Reserved", "Amber")

MAX('TL Forecast Bookings'[Booking Type] = "Vacation/Holiday", "Yellow")

MAX('TL Forecast Bookings'[Booking Type] = "Training/Exams", "Violet")

MAX('TL Forecast Bookings'[Booking Type] = "Non Chargeable", "Azure")

BLANK()

)

Any help would be massively appreciated.

[DataInsights · Accepted solution]

@JoeVincent,

Try this measure:

Colour by category =
SWITCH (
    MAX ( 'TL Forecast Bookings'[Booking Type] ),
    "Chargeable", "LightGreen",
    "Provisional/Reserved", "Amber",
    "Vacation/Holiday", "Yellow",
    "Training/Exams", "Violet",
    "Non Chargeable", "Azure"
)

[JoeVincent]

Thank you! Ended up continuing to tweak mine while I was waiting for a response and found that this also works (changed some colours due to preference):

Colour by category =

SWITCH (TRUE (),

MAX('TL Forecast Bookings'[Booking Type]) = "Chargeable", "LightGreen",

MAX('TL Forecast Bookings'[Booking Type]) = "Provisional/Reserved", "Orange",

MAX('TL Forecast Bookings'[Booking Type]) = "Vacation/Holiday", "Yellow",

MAX('TL Forecast Bookings'[Booking Type]) = "Training/Exams", "Violet",

MAX('TL Forecast Bookings'[Booking Type]) = "Non Chargeable", "DodgerBlue",

MAX('TL Forecast Bookings'[Booking Type]) = "Internal Project", "DodgetBlue"

)

[FreemanZ]

hi @JoeVincent @DataInsights 

it seems there is one argument too much or less,

Colour by category =

SWITCH (

    MAX ( 'TL Forecast Bookings'[Booking Type] ),

    "Chargeable", "LightGreen",

    "Provisional/Reserved", "Amber",

    "Vacation/Holiday", "Yellow",

    "Training/Exams", "Violet",

    "Non Chargeable", "Azure",

    BLANK()

)

or

Colour by category =

SWITCH (

    MAX ( 'TL Forecast Bookings'[Booking Type] ),

    "Chargeable", "LightGreen",

    "Provisional/Reserved", "Amber",

    "Vacation/Holiday", "Yellow",

    "Training/Exams", "Violet",

    "Azure"

)