Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
punksterz626
Helper II
Helper II

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

RegionContact OrderP1P2P3

EAST

A1OUTPENDPEND
WESTB2COMPOUTCOMP
NORTHC3PEND OUT
SOUTHD4 COMP 
1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @punksterz626 ,

According to your description, heres 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 .

vkalyjmsft_0-1637057395278.png

 

2.you only need to write one measure:

Color = SWITCH(MAX('Table (2)'[Value]),"OUT","RED","COMP","YELLOW","PEND","GREEN")

vkalyjmsft_1-1637057395280.png

 

3.Alough the data view differs, the report view still can remain the same by a matrix like this.

vkalyjmsft_2-1637057395283.png

 

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.

View solution in original post

8 REPLIES 8
v-yanjiang-msft
Community Support
Community Support

Hi @punksterz626 ,

According to your description, heres 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 .

vkalyjmsft_0-1637057395278.png

 

2.you only need to write one measure:

Color = SWITCH(MAX('Table (2)'[Value]),"OUT","RED","COMP","YELLOW","PEND","GREEN")

vkalyjmsft_1-1637057395280.png

 

3.Alough the data view differs, the report view still can remain the same by a matrix like this.

vkalyjmsft_2-1637057395283.png

 

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.

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. 

Hi @punksterz626 ,

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

vkalyjmsft_0-1639099285963.png

vkalyjmsft_1-1639099295895.png

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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?

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
Super User
Super User

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.  


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

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

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.