Forum Discussion
solving a query
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:
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.
4 Replies
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 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 toTask/Story start date passed = IF(ISBLANK(Task[start_date_10239]),"No", IF(Task[start_date_10239]<=TODAY() && Task[ISSUE_STATUS_NAME]<>"new","No","Yes"))?
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.