Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX Support - Switch statement - Colouring for conditional formatting

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.
Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    Anonymous,

     

    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"
)

3 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    3 years ago

    Anonymous,

     

    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"
)
    • FreemanZ's avatar
      FreemanZ
      Super User
      3 years ago

      hi Anonymous 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"

      )

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago
       
      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"
      )