Forum Discussion

Sonia316's avatar
Sonia316
Helper I
4 years ago

Conditional Formatting Issues - Fields are dropdowns

See Data here:  https://docs.google.com/spreadsheets/d/1mOAo4i0i1-Q4WqyvltyytXzOCEIdhmOq/edit?usp=sharing&ouid=111553023038978528647&rtpof=true&sd=true

 

Profile Master as you see has sets of values based on the Profile selected, but Profile is a drop down selection.  I thought about conditional formatting on the values for each type (tamperhump, etc), but they change based on Profile.

 

I want to show colors based on that.... so based on Profile - the colors would be red/gree

 

Need Help

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    Hi Sonia316 ,

     

    For this you need to create a calculation based on the values of the profiles and on the maximum and minimums, for each of the metrics.

     

    Can you please confirm that the names on the colours are the ones corresponding to the columns names on the page Quality Check?

    • Sonia316's avatar
      Sonia316
      Helper I
      4 years ago

      Thanks MFelix - I could use all the help possible.

       

      The JSON I am using in SharePoint looks like this - red, yellow, no color.

       

      The values are kept in another table called Profile Master.  Using WidthTop and Width Bottom would have the same code.

       

      Their colums are as you see below - there are 4, WidthA being Red, Width B&C are yellow, and WidthD is plain.  

       

      "background-color": "=if(@currentField == '', 'rgba(0, 0, 0, 0.3)', if(@currentField < [$Profile_x003a_IRVWidthA], 'rgba(240, 52, 52, 1)', if(@currentField <= [$Profile_x003a_IRVWidthB], 'rgba(255, 255, 126, 1)', if(@currentField < [$Profile_x003a_IRVWidthC], 'rgba(255, 255, 255, 1)', if(@currentField <= [$Profile_x003a_IRVWidthD], 'rgba(255, 255, 126, 1)', 'rgba(240, 52, 52, 1)')))))"


      },

       

      I am new to PowerBi and I've been struggling for over a week.  I was hoping to get this report done for this Sprint.

    • Sonia316's avatar
      Sonia316
      Helper I
      4 years ago

      I created this measure for one - 

       

      Color Transcend2Trop1x5.5GR =
      SWITCH (
      TRUE(),
      SELECTEDVALUE('Profile Master'[Title] = Transcend 2.0 Trop 1x5.5 GR),
      'Quality Check'[WidthTop] <5.535, "red",
      'Quality Check'[WidthTop] <=5.54, "yellow",
      'Quality Check'[WidthTop] <=5.6, "yellow",
      'Quality Check'[WidthTop] <5.595, "white"
      )
       
      But it's not recognizing the field WidthTop.
       
      These are my ranges                                Red                   Yellow               White
       
      Transcend 2.0 Trop 1 X 5.5 GR> 5.6005.595 to 5.6005.594 to 5.541
       < 5.5355.535 to 5.540 
  • Sonia316's avatar
    Sonia316
    Helper I
    4 years ago

    color2 =
    SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Profile Master'[Title] ) = "Transcend 2.0 Trop 1x5.5 GR"
    && SUM('Quality Check'[WidthTop]) >5.600
    && SUM('Quality Check'[WidthTop]) <5.535, "red",
    SELECTEDVALUE ( 'Profile Master'[Title] ) = "Transcend 2.0 Trop 1x5.5 GR"
    && SUM('Quality Check'[WidthTop]) >5.595
    && SUM('Quality Check'[WidthTop]) <5.600, "yellow",
    SELECTEDVALUE ( 'Profile Master'[Title] ) = "Transcend 2.0 Trop 1x5.5 GR"
    && SUM('Quality Check'[WidthTop]) >5.535
    && SUM('Quality Check'[WidthTop]) <5.540, "yellow",
    SELECTEDVALUE ( 'Profile Master'[Title] ) = "Transcend 2.0 Trop 1x5.5 GR"
    && SUM('Quality Check'[WidthTop]) >5.587
    && SUM('Quality Check'[WidthTop]) <5.549, "white",
    "#FF0000"
    )

     

    I believe this will work but I do not know how to apply it, I will have to build this for EACH profile name, but how to set it as a conditional format?