Forum Discussion

NOVICE02's avatar
NOVICE02
Helper III
6 years ago

Conditional formatting based on another table

I cannot figure this thing out and i feel that it is too simple. I have data that I direct query and have put into a table to look at month to date values by different metrics. I have created a targ...
Need Help
vivran22's avatar
vivran22
Community Champion
6 years ago

Hello NOVICE02 

 

Conditional formatting sometimes is tricky in Power BI, especially when you want to involve multiple tables.

 

There could be multiple ways of doing it, but the way I do it is by creating a measure for defining the color:

 

Color AHT = 
SWITCH (
    TRUE (),
    ISBLANK ( [tgt AHT] ), "#C0C0C0",
    [Avg Handle Time] <= [tgt AHT], "#008000",
    [Avg Handle Time] > [tgt AHT], "#f08080"
)

 

In the example above, I am comparing two measures: [Avg Handle Time] against [Aht target] which are in two separate tables.

 

Once this measure is created, apply the following steps in conditional formatting :

Format by > Field Value

 

 

Then select the measure created above:

 

 

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

  • NOVICE02's avatar
    NOVICE02
    Helper III
    6 years ago

    Thanks Vivek,

     

    I will try this and get back. Hoping its not too tedious as i have multiple variance variables

  • NOVICE02's avatar
    NOVICE02
    Helper III
    6 years ago

    it didn't work... I wrote a measure to test like this:

    Live_pen =
    SWITCH (
    TRUE (),
        ISBLANK ( [% liv_pen_var] ), "#BBE4EC",
        [% liv_pen_var] < 0 , "#DA291C" ,[% liv_pen_var] = 0 ,"#FFBF3F",[% liv_pen_var] > 0, "#00B050")
     
    and followed the same steps to apply formatting based on above field value but the value is unchanged.
    • vivran22's avatar
      vivran22
      Community Champion
      6 years ago

      NOVICE02 

       

      I am not sure how you are applying it. I have used the same formula and it is working for me:

       

       

      Can you share more details on this?

       

      Cheers!
      Vivek


      If it helps, please mark it as a solution
      Kudos would be a cherry on the top 🙂


      https://www.vivran.in/

      Connect on LinkedIn

      • NOVICE02's avatar
        NOVICE02
        Helper III
        6 years ago

        Once I write the measure.. I choose the able and select var. penetration... which is the measure defined in:

        Live_pen =

        SWITCH (
        TRUE (),
            ISBLANK ( [% out_liv_pen_var] ), "#BBE4EC",
            [% out_liv_pen_var] < 0 , "#DA291C" ,[% out_liv_pen_var] = 0 ,"#FFBF3F",[% out_liv_pen_var] > 0, "#00B050")

         

         

        From conditional formatting i select format by field value and choose the measure that i created and click ok...

         

        and nothing... am i missing a step ?

         

         

  • condolar's avatar
    condolar
    Helper I
    3 years ago

    Hi vivran22 ,

     

    Thank you for this solution, it is exactly what I have been looking for!!

     

    I have one futher question, is it possible to add a third colour to this? I wish to format the cells with the rules

     

    Actual >= Target

    Actual < Target

    Actual <0.7*Target

     

    In a Red Amber Green format. Your solution works for two colours, I did try adding another rule but it didn't work. Could you help?

     

    Thanks,

    Connor

    • condolar's avatar
      condolar
      Helper I
      3 years ago

      Actually, I think I have found a solution! I use the following to acheive what I wanted:

       

      NotebookCF = 
SWITCH(
    TRUE (),
    ISBLANK ( [Notebooks Target] ), "#C0C0C0",
    //met or exceeded
    [Notebooks Created] >= [Notebooks Target], "#85e085",
    //more than 70% there
    {[Notebooks Created] < [Notebooks Target]&&[Notebooks Created]>0.7*[Notebooks Target]}, "#eead0e",
    //less than 70% there
    [Notebooks Created] < 0.7*[Notebooks Target], "#f08080"
)