Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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!
Need Help
hashah's avatar
hashah
Advocate II
7 years ago

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 :)
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
    Anonymous
    Not applicable
    7 years ago

    Perfect, thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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!

     

     

  • kinan82's avatar
    kinan82
    Frequent Visitor
    4 years ago

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

  • Greg888's avatar
    Greg888
    Helper I
    4 years ago

    This is genius thank you!

  • Ram107568's avatar
    Ram107568
    Frequent Visitor
    3 years ago

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