Help with multiple conditions for DATES conditional formatting

[Anonymous]

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?  

[v-xiaotang]

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]

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

[v-xiaotang]

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]

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 · Accepted solution]

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.

[Anonymous]

This is what I'm looking to do in PBI...  https://chandoo.org/wp/highlight-due-dates-excel/   Maybe there is a way to convert the excel formulas to DAX?