Forum Discussion

GabrielM's avatar
GabrielM
New Member
9 years ago
Solved

Count status with conditional dates

Hi, I have a challenge and I need your help. I have a status log per tasks, in which I have the record of the changes status of each task. For example, task "A" goes through states 1, 2 and...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    GabrielM

     

    In this scenario, I assume you have a Date/Time to get those certain moments. However, since there's no direct relationship between these certain moments and your source table. You can't include the fields from these two tables into same chart visual.

     

    For your requirement, you can only create a slicer for those certain moments, the build a measure to get the last status for each task.

     

    Last Status = 
var MaxTime = CALCULATE(MAX(Table3[Time]),FILTER(Table3,Table3[Time]<=MAX(Table4[Time])))
return
CALCULATE(MAX(Table3[Status]),FILTER(Table3,Table3[Time]=MaxTime))

     

     

    And to calculate the number of tasks for each status, we have to summarize above table into calculated table. However, it's not possible to generate a dynamic table. And we can't assign different aggregation on different hierarchy level. So these calculation can't be explored on different level.

     

    Rergards,

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

GabrielM

 

In this scenario, I assume you have a Date/Time to get those certain moments. However, since there's no direct relationship between these certain moments and your source table. You can't include the fields from these two tables into same chart visual.

 

For your requirement, you can only create a slicer for those certain moments, the build a measure to get the last status for each task.

 

Last Status = 
var MaxTime = CALCULATE(MAX(Table3[Time]),FILTER(Table3,Table3[Time]<=MAX(Table4[Time])))
return
CALCULATE(MAX(Table3[Status]),FILTER(Table3,Table3[Time]=MaxTime))

 

 

And to calculate the number of tasks for each status, we have to summarize above table into calculated table. However, it's not possible to generate a dynamic table. And we can't assign different aggregation on different hierarchy level. So these calculation can't be explored on different level.

 

Rergards,