solving a query

[Anonymous]

Hi All,

I have a question on how to solve the problem i face.

I'm querying my data set for the date and its status, the status could be BLANK, new, in progress, in refinement or done, based on the date, the response needs to be yes or no.

What i have so far:

test = IF(ISBLANK(Task[DUE_DATE]),"No", IF(Task[DUE_DATE]<=TODAY() && Task[ISSUE_STATUS_NAME]<>"In progress","Yes","No"))

What i need help with is how to capture the other statuses, so if the due date has passed, the status cannot or at least should not be BLANK, new, in progress or in refinement.

Anyone have an idea on how to solve this?

Thanks,

Jeff

[Anonymous · Accepted solution]

Yes. Perhaps you can first put down all the combination on the Excel and create a VALID map

Task StartDate / Due Date / Task Status Name / IsValid / Message

Then you can use this to write a proper DAX or PowerQuery. If you need help, you can share all the combinations and then I can provide you the syntax. I presonally think PowerQuery Computed Column would be the right approach for you.

[Anonymous]

I'll try it and see what happens, thanks for the help.

Jeff

[Anonymous]

I reckon you would need 2 columns

Column 1 - would be simple Yes,No, NA depending on various status and date combination validity. like below

Then you can use this column to check which rows have valid status and which rows dont have valid status.

Check Status isValid = 
    IF(ISBLANK(Task[DUE_DATE])
        ,"NA"
        , IF(Task[DUE_DATE]<=TODAY(),
            IF( Task[ISSUE_STATUS_NAME]<>"In progress"
                ,"Yes"
                ,"No"
            )
        )
    )

Column 2 - will be actual Error messages if Date and Status combination is not valid

Check Status Info = 

    IF(ISBLANK(Task[DUE_DATE])
        ,"Due Date is Blank"
        , IF(Task[DUE_DATE]<=TODAY(),
            IF( Task[ISSUE_STATUS_NAME]<>"In progress"
                ,"Valid"
                ,"Status Should be in Progress"
            )
        )
    )
    
    

Does this help or would you need more DAX help?

[Anonymous]

@Anonymous I thought about this some more and realised that if the due date had passed, then the status had to be done, so i removed the in progress.

This query is the one I should have put in my original question:

Task/Story start date passed = IF(ISBLANK(Task[start_date_10239]),"No", IF(Task[start_date_10239]<=TODAY() && Task[ISSUE_STATUS_NAME]<>"In progress","Yes","No"))

This needs to be able to capture in progress, in refinement and done as yes and new as no, so maybe I should change this to

Task/Story start date passed = IF(ISBLANK(Task[start_date_10239]),"No", IF(Task[start_date_10239]<=TODAY() && Task[ISSUE_STATUS_NAME]<>"new","No","Yes"))?