Ontrack DAX Expression

[amaoa]

Hello All,

So I wrote the DAX expressions for the different task status count by date. But my Ontrack expression is not all inclusive or not capturing all the different counts of tasks status dates for Ontrack tasks. See below expressions I wrote for all the different Status (complete, late, not started & ontrack) counts. My Ontrack is missing some scenarios or conditions and I can't wrap my head around which or how to make my expression all inclusive. The sample data used that is not showing correctly is shown below. The first image shows 16 ontrack tasks but my expression displays only 15 counts. The second image shows 21, but my expression displays only 20 counts. I will appreciate any insight or feedback into the correct DAX expression that captures all the different scenarios that shows the correct counts.

Complete: TaskPercent Completed = 100%.  Easiest of all.

Late = SUMX(Tasks, IF (AND(AND(TaskFinishDate < TODAY(), TaskPercentComplete<> 100), TaskFinishDate <> BLANK()), 1,0)

or = SUMX(Tasks, IF (AND(TaskStartDate < TODAY (), TaskFinishDate< TODAY()), 1,0)

Not Started = SUMX(Tasks, IF (AND (AND (TaskStartDate< TODAY (), TaskPercentComplete = 0), TaskFinishDate> TODAY()), 1,0)

Ontrack = SUMX(Tasks, IF(OR(AND(TaskStartDate<TODAY(), TaskPercentComplete>0), TaskFinishDate>TODAY()), IF (AND(TaskStartDate>TODAY(), TaskFinishDate>TODAY()), 1,0)

image1  image 2

[amitchandak]

@amaoa , The definition of not started and Late has overlap with on time.

Try if this can work, new column

switch (True(),
[TaskPercentComplete] = 1 , "Complete",
TaskStartDate<TODAY() && TaskPercentComplete>0 && TaskFinishDate>TODAY() , "Ontime",
AND(TaskStartDate>TODAY(), TaskFinishDate>TODAY()) , "Not Started",
"Late"
)

New column for count of Ontime

switch (True(),
[TaskPercentComplete] = 1 , 0,
TaskStartDate<TODAY() && TaskPercentComplete>0 && TaskFinishDate>TODAY() , 1,
0
)

[amaoa · Accepted solution]

I added more details to my original question. I guess I was not clear enough.

I already have a column that shows status as shown in the two images. I just need to count how many task is Ontrack. I tried writing an expression based on the dates and % comp, but I guess I could have just written a new measure to count the Status of "Ontrack" tasks instead of trying to use the dates and % comp columns. Thanks

[Greg_Deckler]

@amaoa - Would need to understand the rules for On Track. But, often moving to a SWITCH(TRUE()...) statement helps clean up the logic:

Ontrack = 
  SWITCH(TRUE(),
    TaskStartDate<TODAY() && TaskPercentComplete>0,"On Track",
    TaskFinishDate>TODAY(),"On Track",
    TaskStartDate>TODAY() && TaskFinishDate>TODAY(),"On Track",
    "Not on track"
  )

[amaoa]

I am trying to count the number of tasks in the images that are ontrack. Not sure if this will help. Below is the full expression. I left some parts out in the original post. 

Ontrack = SUM(Tasks, IF....... , 1,0)))

Thanks