Forum Discussion

NOVICE02's avatar
NOVICE02
Icon for Helper III rankHelper 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
Icon for Community Champion rankCommunity 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/

condolar's avatar
condolar
Icon for Helper I rankHelper 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
    Icon for Helper I rankHelper 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"
)