Forum Discussion
HOW TO: conditional format table with multiple text columns
Hello All,
I was wondering if there is a way to conditional format multiple text columns to yield the following color scheme in a table? The only way I can think of is creating a measure for each of the columns and using the switch to get them into numbers and then using the conditional format function. The problem is I have A LOT of columns. Is there an easier way?
OUT RED
PEND YELLOW
COMP GREEN
BLANK = NO COLOR
| Region | Contact | Order | P1 | P2 | P3 |
EAST | A | 1 | OUT | PEND | PEND |
| WEST | B | 2 | COMP | OUT | COMP |
| NORTH | C | 3 | PEND | OUT | |
| SOUTH | D | 4 | COMP |
Hi Anonymous ,
According to your description, here’s my solution.
1.Unpivot columns of “P1,P2,P3”.In this case, the layout of the data view will change, however you can also use a duplicated new table to achieve it .
2.you only need to write one measure:
Color = SWITCH(MAX('Table (2)'[Value]),"OUT","RED","COMP","YELLOW","PEND","GREEN")
3.Alough the data view differs, the report view still can remain the same by a matrix like this.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
Hi,
In the Query Editor, you should select the first 3 columns and then use the "Unpivot other columns". You will then have to write only 3 measures - one each for Out, Pend and Comp.
Hope this helps.
Hi,
I don;t think transforming the data in a certain way will affect the desired result. Please try.
Hi Anonymous ,
According to your description, here’s my solution.
1.Unpivot columns of “P1,P2,P3”.In this case, the layout of the data view will change, however you can also use a duplicated new table to achieve it .
2.you only need to write one measure:
Color = SWITCH(MAX('Table (2)'[Value]),"OUT","RED","COMP","YELLOW","PEND","GREEN")
3.Alough the data view differs, the report view still can remain the same by a matrix like this.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
According to your error message, I suggest you check your formula again, whether you add bracket after MAX function like below.
MAX('Table (2)'[Value])
Best Regards,
Community Support Team _ kalyj
Hi Anonymous ,
You should click on the "+" sign, it will unfold.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
