topic Re: Return a specific value in column when meeting a criteria in DAX Commands and Tips

Return a specific value in column when meeting a criteria

PC2022 — Fri, 16 May 2025 13:25:57 GMT

I need to write DAX to show which milestone task (highlighted) is causing the schedule to be late. For example, the task is "100% PPT Review of Complete Package" is behind schedule causing the entire project to be late

I need the output to say "100% PPT Review of Complete Package" is 91 days (calculated from today) late.

This is my DAX to calculate project schedule status.

Schedule Status =

VAR CurrentTaskOffScheduleCount = CALCULATE([Count of Off Schedule Tasks], msdyn_projecttask[Is Next Task] = TRUE())

VAR CurrentTaskAtRiskCount = CALCULATE([Count of At Risk Tasks], msdyn_projecttask[Is Next Task] = TRUE())

VAR ProjScheduleStatus = SWITCH(TRUE(),

msdyn_project[Baseline Start] = BLANK() && msdyn_project[Baseline Finish] = BLANK(), "",

CurrentTaskOffScheduleCount > 0, "Off Schedule",

CurrentTaskAtRiskCount > 0, "On Watch",

"On Schedule")

RETURN ProjScheduleStatus

I was trying DAX below but it brings up ALL late tasks. I need to show only the first milestone task that is late.

OnTimeStatus = IF(msdyn_projecttask[Schedule Status] = "Off Schedule", msdyn_projecttask[Project Task Name])," - ")

Re: Return a specific value in column when meeting a criteria

DataNinja777 — Fri, 16 May 2025 15:32:21 GMT

Hi PC2022 ,

To return the name of the first milestone task that is currently late and calculate how many days it’s overdue, you can use the following DAX measure. This measure filters for tasks that are marked as "Off Schedule", not 100% complete, and have a valid finish date. It then finds the task with the earliest finish date among those, calculates how late it is compared to today’s date, and returns the task name along with the number of days late.

FirstLateMilestoneTask = VAR TodayDate = TODAY() VAR LateTasks = FILTER( msdyn_projecttask, msdyn_projecttask[Schedule Status] = "Off Schedule" && msdyn_projecttask[% Complete] < 100 && NOT(ISBLANK(msdyn_projecttask[Finish Date])) ) VAR FirstLateTask = TOPN( 1, LateTasks, msdyn_projecttask[Finish Date], ASC ) VAR TaskName = MAXX(FirstLateTask, msdyn_projecttask[Project Task Name]) VAR FinishDate = MAXX(FirstLateTask, msdyn_projecttask[Finish Date]) VAR DaysLate = DATEDIFF(FinishDate, TodayDate, DAY) RETURN IF( NOT ISBLANK(TaskName), TaskName & " is " & DaysLate & " days late", BLANK() )

This returns only one result: the name of the first late milestone task and how many days late it is.

Best regards,

Re: Return a specific value in column when meeting a criteria

Anonymous — Mon, 19 May 2025 07:11:32 GMT

Hi PC2022 ,
Thank you for reaching out to the Microsoft fabric community forum.

I wanted to check if you had the opportunity to review the information provided by DataNinja777 . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Re: Return a specific value in column when meeting a criteria

PC2022 — Tue, 20 May 2025 15:03:32 GMT

Thank you so much! It worked!