Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Color Coding with IF in Power BI

I am trying to color code a column in my table with an if statement.   I keep getting an error message.  Any help would be greatly appreciated.  Here is my Dax formula.  

=IF(AND([Calibration Due]]>reportdate,[Calibration Due]]<TODAY()+90),"Due Within 90 Days",IF([Calibration Due]]>TODAY,"In Calibration", IF([Calibration Due]]>TODAY() <> 90, "Green",  IF([Calibration Due]]>TODAY,"Yellow",

"CALIBRATION)

 

 

Show and Tell
Tips and Tricks

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , I think you missed two things. color measure text measure should be separate

    example measures

    =IF(AND([Calibration Due]]>reportdate,[Calibration Due]]<TODAY()+90),"Due Within 90 Days",IF([Calibration Due]]>TODAY,"In Calibration",
    "CALIBRATION"))

     

    color =

    IF([Calibration Due]]>TODAY() <> 90, "Green", IF([Calibration Due]]>TODAY,"Yellow", "white"))

     

    How to do conditional formatting by measure and apply it on pie? : https://youtu.be/RqBb5eBf_I4

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I am new to Power Query.  I tried to add this column  I am getting error message "Token Literal expected".  Here is my DAX in power query 

      = Table.AddColumn(#"Changed Type1", "CalDueFormat", each if [Calibration Due] > Date.From(DateTime.LocalNow()) & if [Calibration Due] < Date.From(DateTime.LocalNow()+ 90 then "Due in 90 Days" else if [Calibration Due] > Date.From(DateTime.LocalNow()) then |
      "In Calibration" else "OverDue")

       

      The color measure you suggested work but wrong colors appear due to my column I added.  It will not mark the overdue rows. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I am new to Power Query.  I tried to add this column  I am getting error message "Token Literal expected".  Here is my DAX in power query 

      = Table.AddColumn(#"Changed Type1", "CalDueFormat", each if [Calibration Due] > Date.From(DateTime.LocalNow()) & if [Calibration Due] < Date.From(DateTime.LocalNow()+ 90 then "Due in 90 Days" else if [Calibration Due] > Date.From(DateTime.LocalNow()) then |
      "In Calibration" else "OverDue")

       

      The color measure you suggested work but wrong colors appear due to my column I added.  It will not mark the overdue rows. 

  • isabella's avatar
    isabella
    Microsoft Employee
    4 years ago

    Hi Anonymous 

     

    you can define your new column like this:

    Column = IF(AND([Calibration Due]>TODAY(),[Calibration Due]<TODAY()+90),"Calibration Due",IF([Calibration Due]>TODAY(),"In Calibration","OverDue"))
    About the measure,

    You can define your measure like this：

     

    Measure 1 = IF(MAX('Table (6)'[Calibration Due])>TODAY()+90,"Green",IF(MAX('Table (6)'[Calibration Due])>TODAY(),"Yellow","White"))

     

     

     

     

    Change the background color by Measure 1

     

     

     

    Visual effect：

     

     

     

    Best Regards,

    Community Support Team _Isabella

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