Forum Discussion
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!
I have found a solution which may help which I got help from one of my team members:
- Create a number value for the word by creating an additional column. For example 'Yes' as '1' and 'No' as '2'.
- Make sure the additonal column value is a whole number and not text.
- Apply and close
- Go to the field, use the drop down in the fields section and choose 'conditional formatting' - choose Background or Font colour.
- Choose 'Rules' for 'Format by'
- 'Summarization' as 'Minimum'
- Choose the number and the colour If value is '1' then 'Green'
- Click OK and it should work :)
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!