Forum Discussion

maku_'s avatar
maku_
Frequent Visitor
5 years ago
Solved

Conditional Formatting Isn't working for two instances

Hello,

 

I'd like to seek for your help with fixing the conditional formatting I created on a table I visualized.

 

What I have here are the following:

  • a table showing "Amber" as the value under "Growth" column
  • The Growth Color which is in hex #FF0000 (Red)
  • Growth count which is two
    • Basically, the instance of "2" means there's two actual record of growth here
      • One is Amber
      • One is Green

 

I created a measure that captures the value of the table (supposedly) and output a hex color to be used for contional formatting:

 
Growth_Color =
VAR Classification = SELECTEDVALUE('DataTable1'[Growth])
RETURN IF(Classification = "Green", "#35CE12", IF(Classification = "Amber", "#E1C233", IF(Classification = "Red","#FF0000","#FF0000")))

 

My Problem:

 

 

If there's two instance of growth, it will output the default color which is "Red" but what I want to do here is if I can reference the visualized table's value to the right color displaying.

In this context it should out put "Amber" #E1C233

 

Sample Data:

 

Mood_Data

Logo_IDLogoMood
1Logo1Red
2Logo2Red
3Logo3Amber
4Logo4Green
5Logo5Red

 

Growth_Data

Logo_IDLogoGrowth
1Logo1Red
2Logo2Amber
2Logo2Red
3Logo3Amber
3Logo3Green
3Logo3Red
4Logo4Green
5Logo5Red

 

Things to do

1. create a matrix, use Logo for rows and user Mood and Growth column for values

2. apply the formula above

Need Help
Show and Tell
Tips and Tricks
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi,  maku_ 

    Try formual as below:

    calculated column:

    color_flag = 
SWITCH ( Growth_Data[Growth], "Green", 1, "Amber", 2, "Red", 3 )
    Classification = 
VAR min_color =
    CALCULATE (
        MIN ( Growth_Data[color_flag] ),
        Growth_Data,
        Growth_Data[Logo_ID] = EARLIER ( Growth_Data[Logo_ID] )
    )
RETURN
    SWITCH ( min_color, 1, "Green", 2, "Amber", 3, "Red" )

    Conditional formatting measure:

    New_Growth_Color =
VAR Classification =
    SELECTEDVALUE ( 'Growth_Data'[Classification] )
RETURN
    IF (
        Classification = "Green",
        "#35CE12",
        IF (
            Classification = "Amber",
            "#E1C233",
            IF ( Classification = "Red", "#FF0000", "#FF0000" )
        )
    )

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    maku_ ,

    Try with this small variation. And Try use Switch( True().

     

    Growth_Color =
    VAR Classification = Max('DataTable1'[Growth])
    RETURN IF(Classification = "Green", "#35CE12", IF(Classification = "Amber", "#E1C233", IF(Classification = "Red","#FF0000","#FF0000")))

     

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • maku_'s avatar
      maku_
      Frequent Visitor
      5 years ago

      amitchandak 

       

      I've uploaded the data table. I can't upload a sample powerbi as the forum doesn't allow it.

       

      Also, for the tweak, you made, it sorta works but not in a dynamic way as it will just pull the last entry

      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi,  maku_ 

        Try formual as below:

        calculated column:

        color_flag = 
SWITCH ( Growth_Data[Growth], "Green", 1, "Amber", 2, "Red", 3 )
        Classification = 
VAR min_color =
    CALCULATE (
        MIN ( Growth_Data[color_flag] ),
        Growth_Data,
        Growth_Data[Logo_ID] = EARLIER ( Growth_Data[Logo_ID] )
    )
RETURN
    SWITCH ( min_color, 1, "Green", 2, "Amber", 3, "Red" )

        Conditional formatting measure:

        New_Growth_Color =
VAR Classification =
    SELECTEDVALUE ( 'Growth_Data'[Classification] )
RETURN
    IF (
        Classification = "Green",
        "#35CE12",
        IF (
            Classification = "Amber",
            "#E1C233",
            IF ( Classification = "Red", "#FF0000", "#FF0000" )
        )
    )

        Best Regards,
        Community Support Team _ Eason
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.