Forum Discussion
Conditional Formatting Direct Query Different Tables
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 🙂
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.
6 Replies
- amitchandak
Super User
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 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.
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.
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.
