Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with multiple conditions for DATES conditional formatting

I need to use all of the conditions listed in the legend (below). I've tried multiple formulas, but nothing seems to work.  It complicates things that "Complete" is noted with a "c" (not a date) and "Not Appicable" is noted with "na".  I understand that these can be turned into a number for conditional formatting, but I don't know how to incorporate that into the rest of the measure. 

 

Also, I would need to use this measure for 36 columns... Is there a way to write a measure that would cover ALL of them?  

 

 

Need Help
Tips and Tricks
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Anonymous 

    since there are date and text in the same column, the column type can only be text in Power BI Desktop, in this scenario I have to do some processing on the column. Here is the solution, create the conditional format measure,

     

    ConditonFormat_A = 
var _compareDate= DATE(2022,3,10) // you can replace it with Today()
var _value= MIN('Table'[Preject A])
/* get date*/
var _position1=if(LEN(_value)>=8,SEARCH("/",_value))
var _position2=if(LEN(_value)>=8,LEN(_value)-5-_position1)
var _Date=if(LEN(_value)>=8, DATE(RIGHT(_value,4),LEFT(_value,_position1-1),MID(_value,_position1+1,_position2)))
/* get datediff*/
var _diffD= if(LEN(_value)>=8,DATEDIFF(_compareDate,_Date,DAY))
var _diffM= if(LEN(_value)>=8,DATEDIFF(_compareDate,_Date,MONTH))
/* get status*/
return 
    IF (
        LEN ( _value ) < 8,
        SWITCH (
            TRUE (),
            CONTAINSSTRING ( _value, "na" ), "black",
            CONTAINSSTRING ( _value, "c" ), "light blue"
        ),
        SWITCH (
            TRUE (),
            _diffD < 0, "red",
            _diffD < 14, "orange",
            _diffD < 30, "yellow",
            _diffM <= 6, "light green"
        )
    )

    Then you need to create conditional formatting measures for other items as well, just change the column names in the third row.

    Here is the result, and please check the sample file attached below.

    Best Regards,

    Community Support Team _Tang

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

     

6 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi Anonymous 

    It's easy to get in Power BI Desktop. See article,

    (1) create a measure,

    background_color = 
VAR _string =
    MIN ( 'Table'[Column1] )
RETURN
    SWITCH (
        TRUE (),
        EXACT ( _string, "c" ), "light blue",
        EXACT ( _string, "na" ), "black",
        EXACT ( _string, "overdue" ), "red"
    )

     

    (2) put the measure into each column

    result

    In order to make the font on the black background white, you need to create another measure

    font_color = if(EXACT ( MIN ( 'Table'[Column1] ), "na" ), "white")

     

    Best Regards,

    Community Support Team _Tang

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      The data, for the most part are dates, not just "na" and "c", so this doesn't work for me.

      • v-xiaotang's avatar
        v-xiaotang
        Community Support
        4 years ago

        Hi Anonymous 

        I've read your question, but information above is not enough to going on. Could you provide more details?

        Usually we need info below

         (1) a sample file, you can replace raw data with bogus data to protect your privacy.

             or provide some sample data that fully covers your issue/question.

        (2) give your expected result based on the sample you provide. If calculation formula is involved, please provide it.

        Kindly note: Please ensure the data in sample is concise and representative.

        Thanks.

         

         

        Best Regards,

        Community Support Team _Tang

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