Forum Discussion

NLewis's avatar
NLewis
Frequent Visitor
4 years ago
Solved

Dax Conditional Formatting

I'm struggling! Can anyone give me the actual Dax formula for the conditons on the bottom screenshot? I'm trying to create a pie chart like the top screenshot (that can give me % totals). I'm lost. I tried

 
BackgroundColor =
SWITCH (
TRUE (),
IF('Measures Table'[TerminalWeeklyQuality] > 0.979 <= 1, "GREEN"),
IF('Measures Table'[TerminalWeeklyQuality] > 0.9599 < 0.979, "YELLOW"),
IF('Measures Table'[TerminalWeeklyQuality] >= 0 < 0.9599, "RED"))

 

 

 

 

Need Help
  • TheoC's avatar
    TheoC
    4 years ago

    Hi NLewis 

     

    I think you're close. Try adjusting to the below:

     

    BackgroundColor =

    VAR _1 = [TerminalWeeklyQuality]

    RETURN

    SWITCH     (
         TRUE (),
            _1 < 0.9599, "RED" ,
            _1 < 0.979, "YELLOW" ,
            "GREEN" )

    The logic with the TRUE() removes the need for an IF statement given, and the between parameters (i.e. > and < ) don't need to be applied because everything under 0.9599 can be red, followed by anything under 0.979 being yellow, and everything else green.  The logic in the SWITCH ( TRUE () ... tests everything in order from start to end.

    So the order is:

    1. If below 0.9599 then RED else

    2. If below 0.979 then YELLOW otherwise

    3. Just make it GREEN and be done with it!

    Hope this helps!

    Theo

     

5 Replies

  • TheoC's avatar
    TheoC
    Community Champion
    4 years ago

    Hi NLewis 

     

    I think you're close. Try adjusting to the below:

     

    BackgroundColor =

    VAR _1 = [TerminalWeeklyQuality]

    RETURN

    SWITCH     (
         TRUE (),
            _1 < 0.9599, "RED" ,
            _1 < 0.979, "YELLOW" ,
            "GREEN" )

    The logic with the TRUE() removes the need for an IF statement given, and the between parameters (i.e. > and < ) don't need to be applied because everything under 0.9599 can be red, followed by anything under 0.979 being yellow, and everything else green.  The logic in the SWITCH ( TRUE () ... tests everything in order from start to end.

    So the order is:

    1. If below 0.9599 then RED else

    2. If below 0.979 then YELLOW otherwise

    3. Just make it GREEN and be done with it!

    Hope this helps!

    Theo

     

    • NLewis's avatar
      NLewis
      Frequent Visitor
      4 years ago

      Thank you TheoC...I made that way more complicated than it was😀

    • NLewis's avatar
      NLewis
      Frequent Visitor
      4 years ago

      It's not working. I says that I need to use VALUE or FORMAT...but when I tried SELECTVALUE(cant use VALUES...all the %'s are not unique)...but I'm getting an error line under my VAR calculation...my variable is a measure...should that matter?

      • TheoC's avatar
        TheoC
        Community Champion
        4 years ago

        NLewis is the [TerminalWeeklyQuality] a Measure or a column?   I assumed measure as it was in a measure table? 

         

        Otherwise, just change it to the following:

         

        BackgroundColor =

        SWITCH (
             TRUE (),
                Table[TerminalWeeklyQuality] < 0.9599, "RED" ,
                Table[TerminalWeeklyQuality] < 0.979, "YELLOW" ,
                "GREEN" )

         

    • NLewis's avatar
      NLewis
      Frequent Visitor
      4 years ago

      1 more question...how do I turn this into a pie chart using the formated % values