How to Evaluate Strings in a Column

[Amaoa83]

Hello all,

Just getting back to PBI after a 2-3yr hiatus. The interface and a lot has changed in PBI. Trying to generate and format cells for the table contents below. Step 1- Step 6 columns are the outputs i am trying to generate from using a function/formula/logical expression. The first 3 columns are fixed. I want the values in columns step 1 to 6 be dates based on the conditions below. Using just Step 1 column as an example below. 

Step 1 column 

If Deadline minus 30 days is less than today AND if the value/string in status column is equal to step 1, then publish the date and format the cell to be yellow (late). Else don't publish a date and cell should be green (early/ontime).

My challenge is how do I get PBI to look at the string values "Step 1" and use that for the evaluation in the logical expression for each row values the expression is being evaluated against? It's not giving me the right output when I use COUNTAX. Any help will be appreciated. Thanks

Step1value = IF(AND(Deadline - 30 < TODAY (), COUNTAX (Status, "Step 1")), Deadline - 30, BLANK ())

[Anonymous · Accepted solution]

Hi @Amaoa83 ,

Modify you calculated column and create below measure:

Step1Value = 
IF (
    AND (
        Projects[Deadline] - 30 < TODAY(),
        Projects[Status] = "Step 1"
    ),
    Projects[Deadline] - 30,
    BLANK()
)

Step1Color = 
IF (
    ISBLANK(MAX(Projects[Step1Value])),
    "Green",
    "Yellow"
)

Best Regards,
Adamk Kong

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