HOW TO: conditional format table with multiple text columns

[punksterz626]

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

[v-yanjiang-msft · Accepted solution]

Hi @punksterz626 ,

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 _ kalyj

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

[punksterz626]

How come when i drag region, contact, and order into rows into a matrix doesn't show up like how you're doing it. It only shows the region. The contact, and order do not show up right next to it as yours do. 

[v-yanjiang-msft]

Hi @punksterz626 ,

You should click on the "+" sign, it will unfold.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

[punksterz626]

Hi,

I get the syntax error "Too many arguments were passed to the MAX function. THe maximum argument count for the function is 2" when using your recommended measure

Color = SWITCH(MAX('Table (2)'[Value]),"OUT","RED","COMP","YELLOW","PEND","GREEN"). Am I doing something wrong?

[v-yanjiang-msft]

Hi @punksterz626 ,

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

[Ashish_Mathur]

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.  

[punksterz626]

Hi Ashish,

Will that affect the layout? It is important that the layout be this specific way

[Ashish_Mathur]

Hi,

I don;t think transforming the data in a certain way will affect the desired result.  Please try.