Forum Discussion

POWER_MI's avatar
POWER_MI
Post Patron
5 years ago
Solved

Switch condition adding color on rows

Hi,

 

  I have question  for same condition and table 

Column = SWITCH ( TRUE (), 'Table'[Step] IN { 1, 2, 3 } && 'Table'[COLOR] IN { "R", "G", "O" }, "R1",

'Table'[Step] IN { 1, 2, 3 } && 'Table'[COLOR] IN { "GR", "O", "Y" }, "R2",

'Table'[Step] IN { 4, 5 } && 'Table'[COLOR] IN { "GR", "O", "Y" }, "R3", "Unknown" )

 

The asking is to add color on table results on Power BI dash bord results for example 

for 

- R1 rows make Red color

- R2 Rows make Green coloe

- R3 rows make Pink Color

 

Anonymous

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi POWER_MI ,

    Since you are using measures to calculate, I still think you need to create each color format for each measure:

    Color_R1 = 
VAR _step =
    SELECTEDVALUE ( Feuil1[STEP] )
RETURN
    SWITCH (
        TRUE (),
         ( _step = 1
            || _step = 2
            || _step = 3 ), "green",
        _step = 5, "orange"
    )
    Color_R2 = 
VAR _step =
    SELECTEDVALUE ( Feuil1[STEP] )
RETURN
    SWITCH ( TRUE (), _step = 1, "green", _step = 4, "orange" )
    Color_R3 = 
VAR _step =
    SELECTEDVALUE ( Feuil1[STEP] )
RETURN
    SWITCH ( TRUE (), _step = 2, "green", _step = 4, "orange", _step = 7, "red" )

     

    Attached the file in the below, hopes to help you.

     

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

17 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    POWER_MI , Create a color measure like

    Measure =
    Switch(true(),
    max(Table[Column]) = "R1" , "Red" ,
    max(Table[Column]) = "R2" , "Green" ,
    max(Table[Column]) = "R3" , "Pink "
    )

     

    and use it in conditional formatting with field value option for the needed columns

     

    https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
    https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi POWER_MI ,

     

    Create a measure

     

    ColorFormat =
SWITCH (
    TRUE (),
    MAX ( 'Table'[Column] ) = "R1", "Red",
    MAX ( 'Table'[Column] ) = "R2", "Green",
    MAX ( 'Table'[Column] ) = "R3", "Pink "
)

     

    Post this follow the steps

     

     

     

     

    Click on Advanced Control

     

     

     

    Select the measure.

     

    Regards,
    Harsh Nathani
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

    • POWER_MI's avatar
      POWER_MI
      Post Patron
      5 years ago

      Hi all 

      thanks for answer, 

       

      in the source table 

      it's like this 

       

      In the Power BI three mesure are calculated based on this table :

       

      Mesure_1_NBR_R1=

      CALCULATE(
          COUNTA('table'[column]),
          'table'[column] IN {"R1"}

      Mesure_2_NBR_R2=

      CALCULATE(
          COUNTA('table'[column]),
          'table'[column] IN {"R2"}

      Mesure_3_NBR_R3=

      CALCULATE(
          COUNTA('table'[column]),
          'table'[column] IN {"R3"}

       

      After that on Power BI dashBoard it's needed to have results like this 

       

       

      Because when i do like explained in answer all rows is Red because i take like reference color column 

       

      ColorFormat =
      SWITCH (
      TRUE (),
      MAX ( 'Table'[Column] ) = "R1", "Red",
      MAX ( 'Table'[Column] ) = "R2", "Green",
      MAX ( 'Table'[Column] ) = "R3", "Pink "
      )

      we will color  'Table'[Column]  but we need to color the values of number of R1,R2,R3 witch calculated with mesure mesure not column 

      Mesure_1_NBR_R1=

      CALCULATE(
          COUNTA('table'[column]),
          'table'[column] IN {"R1"}

      Mesure_2_NBR_R2=

      CALCULATE(
          COUNTA('table'[column]),
          'table'[column] IN {"R2"}

      Mesure_3_NBR_R3=

      CALCULATE(
          COUNTA('table'[column]),
          'table'[column] IN {"R3"}
       
      when doing 
       
      ColorFormat =
      SWITCH (
      TRUE (),
      MAX ( 'Table'[Mesure_1_NBR_R1] ) = "R1", "Red",
      MAX ( 'Table'[Mesure_2_NBR_R2] ) = "R2", "Green",
      MAX ( 'Table'[Mesure_3_NBR_R3] ) = "R3", "Pink "
      )
      the error is 
      Column "Mesure_1_NBR_R1" in table "Table" could not be found and is not used in this expression.
      Column "Mesure_2_NBR_R2" in table "Table" could not be found and is not used in this expression.
      Column "Mesure_3_NBR_R3" in table "Table" could not be found and is not used in this expression.
       
      I means to say that we need to color Number of  and not colum values.
      Number of R1, Number of R2 and R3.
      thanks
      • POWER_MI's avatar
        POWER_MI
        Post Patron
        5 years ago

        Hi all 

        i checked one thing : 

         

        when mesure is calculation one icone like this  the color format is not working 

        but when it's like this  it's work fine.

         

        For our demande it's number so calculate mesure 

        have you solution thanks 

         

  • POWER_MI's avatar
    POWER_MI
    Post Patron
    5 years ago

    Hi v-yingjl 

     You can find detail here 

    Here the Values is Number of R1, Number of R2 and Number of R3

    We used three mesure to calculte this 

    mesure 1=CALCULATE( COUNT('table'[column]), 'table'[column] IN "R1")

    mesure 2=CALCULATE( COUNT('table'[column]), 'table'[column] IN "R2")

    mesure 3=CALCULATE( COUNT('tablae[column]), 'table'[column] IN "R3")

    Is it possible to make one Mesure to calculate number R1 and R2 and R3?

     

    Also the Value on table will be number of R1,R2,R3 depeding on STEP i mean 

     

    if step 1 && 'table'[column]="R1" color Green

    if step 2 && 'table'[column]="R1" color Green

    if step 3 && 'table'[column]="R1" color Green

     

    if step 4 && 'table'[column]="R1" color ORANGE

    if step 5 && 'table'[column]="R1" color ORANGE

    if step 6 && 'table'[column]="R1" color RED

     

     

     

     

    • POWER_MI's avatar
      POWER_MI
      Post Patron
      5 years ago

      this results need



      you can find Power Bi project in this Link ( you click on Telecharger le fichier " donwlowd file
      you can update file Power Bi and sent it
      Link to download file : click here

      • v-yingjl's avatar
        v-yingjl
        Community Support
        5 years ago

        Hi POWER_MI ,

        Since you are using measures to calculate, I still think you need to create each color format for each measure:

        Color_R1 = 
VAR _step =
    SELECTEDVALUE ( Feuil1[STEP] )
RETURN
    SWITCH (
        TRUE (),
         ( _step = 1
            || _step = 2
            || _step = 3 ), "green",
        _step = 5, "orange"
    )
        Color_R2 = 
VAR _step =
    SELECTEDVALUE ( Feuil1[STEP] )
RETURN
    SWITCH ( TRUE (), _step = 1, "green", _step = 4, "orange" )
        Color_R3 = 
VAR _step =
    SELECTEDVALUE ( Feuil1[STEP] )
RETURN
    SWITCH ( TRUE (), _step = 2, "green", _step = 4, "orange", _step = 7, "red" )

         

        Attached the file in the below, hopes to help you.

         

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