Conditional Formatting Direct Query Different Tables

[Anonymous]

Greetings,

I have two columns in a data table in Power BI from different tables using direct query:

• TableOne[Status]
• TableTwo[Instances]

I pull TableOne[Status] into the first column and then TableTwo[Instances] and then count the number of statuses based on the instances. The status columns contain Completed, Errored & Running. I want to change the colour of the counts based on the status such as:

• Completed = Green
• Running = Grey
• Errored = Red

Everything I am seeing thus far in Power BI is all about when a value is greater than, other than when the text is equal to X then apply the colour. Any pointers would be greatly appreciated 🙂

[Anonymous · Accepted solution]

Hi  @Anonymous ,

According to your description, I created some data to show:

Here are the steps you can follow：

1. Create measure.

Measure =
SWITCH(
    TRUE(),
     MAX('Table'[states])="Completed","Green",
     MAX('Table'[states])="Running","Grey",
     MAX('Table'[states])="Errored","Red"
    )

 2. Find format, search background color in the search bar, find conditional formatting, select the column name to change color, and turn on.

3. Select field value in format by and new measure in based on field

4. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Awesome, thank you, this has solved my problem 🙂

[Anonymous]

Hi  @Anonymous ,

According to your description, I created some data to show:

Here are the steps you can follow：

1. Create calculated column.

states_id =
SWITCH(
    TRUE(),
    'Table'[states]="Completed",1,
    'Table'[states]="Running",2,
    'Table'[states]="Errored",3)

2. Find format, search background color in the search bar, find conditional formatting, select the column name to change color, and turn off to on.

3. The background color interface appears

• Select format by as rules
• Change the condition of the following rules to is and number, and select the color
• After clicking new rule, continue the above operation

4. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks,

However, this creates a new column and I already have a column with the counts. Below is my visualization, I want the number of records to be coloured based on the status but each column is from another table. So if the status is Error I want the count to be Green, Amber or Red depending on the string.

[amitchandak]

@Anonymous , Create a color measure like this and use that in conditional formatting with the field value option

switch(true() ,
max(Table[status]) = "Completed" , "Green",
max(Table[status]) = "Running" , "Grey"
max(Table[status]) = "Errored" , "Red"
)

steps

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]

Thanks for your reply,

I have since tried to apply this using the links given but the table does not update. Any suggestions?