cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Conditional formatting for a text column

Hi PBI community,

 

Is there a way to change the background or font color of a table cell for a text based column using DAX.

 

Any help would be appreciated.

 

Thank you!

21 REPLIES 21
d_rohlfs
Regular Visitor

If you are looking to use DAX, here is an easy formula to use. 

 

Title =
VAR Name1 = IF(SELECTEDVALUE('TABLE'[COLUMN]) = "TEXT", True, False)
VAR Name2 = IF(SELECTEDVALUE('TABLE'[COLUMN]) = "TEXT", True, False)
VAR Name3 = IF(SELECTEDVALUE('TABLE'[COLUMN]) = "TEXT", True, False)

RETURN

SWITCH(
TRUE(),
Name1 , "COLOR",
Name2, "COLOR",
Name3, "COLOR")

 

You would just need to type in the table names and columns. This method would require you to type in all of the cell contents that you want to format, but it works great for smaller distinct value distributions. 

MaliniB
Helper II
Helper II

Hi, I am working on a similar stuffs where I need the text to have colors. I have a rating column ratings=0 are detractors, raating=1 are promotors and rating =2 are passive. So I want the comments(text) associated with this ratings to follow the same color but it is not working is there a way to solve the problem

mh2587
Super User
Super User

You can use it with switch function and color codes
XYZ Color = SWITCH(TRUE(),
                      X < 0,"#FF0000",
                   Y = 0, "#FFFF00",
                 Z >0,       "#00FA9A",
                                "#00FA7A"          )


Muhammad Hasnain

#LetsConnect https://www.linkedin.com/in/hasnain2587/

This method works perfectly in the Sept. 2021 version. Simply create a Calculated Column based on the original column using the method above (Hex color codes). Then do conditional formattting on the original field in a table or matrix and pick "Format by" > "Field value", "Based on field" > Your calculated column.

LiamWhite
Advocate I
Advocate I

This post provides one method: https://exceleratorbi.com.au/conditional-formatting-with-a-text-field-in-power-bi/

 

The DAX is essentially:

Colour Test =
VAR Dept = SELECTEDVALUE(Projects[Department])
RETURN IF(Dept <> BLANK(), "Green", "Red")
 
 
hashah
Advocate II
Advocate II

I have found a solution which may help which I got help from one of my team members:

 

  1. Create a number value for the word by creating an additional column. For example 'Yes' as '1' and 'No' as '2'.
  2. Make sure the additonal column value is a whole number and not text.
  3. Apply and close
  4. Go to the field, use the drop down in the fields section and choose 'conditional formatting' - choose Background or Font colour.
  5. Choose 'Rules' for 'Format by'
  6. 'Summarization' as 'Minimum'
  7. Choose the number and the colour If value is '1' then 'Green'
  8. Click OK and it should work 🙂

This is a perfect and simple way. Thanks so much. You saved a lot of my time.

This is genius thank you!

This is a huge discovery for me. Thank you!!!

This one helped me solve a problem, one that blocked me all day, many thanks! I got as far as you did up to creating a column to be numbers assigned from another column that is Red, Amber, Green until the conditional formatting lost me, but you helped me solve that one.

 

The business logic was to calculate the number of months between now and a future date, then using business rules to create a RAG upon them.

 

Red -> Months to Expiry <=12

Amber -> Months to Expiry >12 and <=24

Green -> Months to Expiry >24

 

Months To Expiry = DATEDIFF(TODAY(),Query1[contract_expiry_date],MONTH)
 
PropRAG = IF(AND(Query1[Months To Expiry]>=0,Query1[Months To Expiry]<=12),"RED",IF(AND(Query1[Months To Expiry]>12,Query1[Months To Expiry]<=24),"AMBER",IF(Query1[Months To Expiry]>24,"GREEN")))
 
PropRAG2 = IF(Query1[PropRAG] == "RED",1,IF(Query1[PropRAG] == "AMBER",2,IF(Query1[PropRAG] == "GREEN",3)))
 
Then used conditional formatting as you outlined within your solution to assign the colours based on the values from PropRAG2 🙂
 
Thanks again!

 

 

Anonymous
Not applicable

Exactly what I was looking for, thanks.  Mind you, being able to apply a format directly to a field based on a text value doesn't seem like it would be that difficult.  It's certainly been something that I could do in Excel for years - come on Microsoft.

Anonymous
Not applicable

Perfect, thanks!

albertop
Regular Visitor

I had the same problem and this is what I did:

 

- Create a new column

- Create an IF formula based on your text column, make it take numbers instead of the values. For example, my text column contained "Yes" and "No". I used the formula New column = IF([Textcolumn]="Yes",1,0).

- Go to the Conditional formatting option and chose to conditional format based on another column, chose your newly created column.

- You can do this with the first two types of conditional formatting: by color scale and by rules.


Anonymous
Not applicable

why is it if i choose conditional formating based on another column it automaticly uses SUM but i do not want it to sum just take the value

As an alternative you can use a measure to retrieve the first value for conditional formatting instead of column aggregation.

 

First Value = FIRSTNONBLANK('Table'[Column 1],'Table'[Column 1])

 
Or try changing the default summarizition for the column in the modeling tab. Click the column, Modeling > Default Summarization
geekymichelle
New Member

The May Update added this functionality. Now, users can conditionally format text and date fields, as long as you choose a numeric value to format on.

geekymichelle
New Member

The  May Update includes the ability to condtionally format text fields, as long as you choose a numeric value to format on.

kevcurtis
Advocate I
Advocate I

This idea is currently "Started":

Conditional formatting (cell highligh, font, etc) when comparing multiple fields

 

I've been needing this functionality for months now.

 

Thanks

Kevin

Anonymous
Not applicable

Thanks for the update @kevcurtis

v-huizhn-msft
Microsoft
Microsoft

Hi @Anonymous,

Up to date, there is no way to change the background or font color of a table cell for a text. Please review the following feature requests and commont on them.

 

Conditional Formatting for Text
Better Conditional Formatting Tools

Best Regards,
Angelia

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.