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 ...
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.

     

v-xiaotang's avatar
v-xiaotang
Icon for Community Support rankCommunity 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
    Icon for Community Support rankCommunity 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Here it is.  This is in excel (data I'm pulling from) and how I need it to look.  The blacked out cells are have "na" in them.  It's not important for those letters to be visible.  There are 36 columns (projects).  Is this helpful towards a solution?  

       

       

      • v-xiaotang's avatar
        v-xiaotang
        Icon for Community Support rankCommunity Support
        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.