Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello. I have a table that register status of tasks in the time.
The problem is: I need to count the tasks open/done using the lastdate selected in the slicer of "date updated"
Eg: when I select slicer the date 10/05/2022 I have this result
status Concluídas = 1
status Abertas = 0
Total = 1
when I select slicer the date 09/05/2022 I have this result
status Concluídas = 0
status Abertas = 1
Total = 1
Now I'm using the following measure, it's ok when I filter only 1 task id. But with all ids not working:
Total Tasks =
VAR Max_Date= CALCULATE(MAX(fTarefas[Date created]),FILTER(ALLSELECTED(fTarefas),fTarefas[Tarefa - ID]=MAX(fTarefas[Tarefa - ID])))
RETURN
CALCULATE(DISTINCTCOUNT(fTarefas[Tarefa - ID]),FILTER(fTarefas,fTarefas[Date created] = Max_Date)) +0
Tasks Done (concluídas) =
VAR Max_Date= CALCULATE(MAX(fTarefas[Date created]),FILTER(ALLSELECTED(fTarefas),fTarefas[Tarefa - ID]=MAX(fTarefas[Tarefa - ID])))
RETURN
CALCULATE(DISTINCTCOUNT(fTarefas[Tarefa - ID]), fTarefas[Tarefa - Status] = "Concluídas", FILTER(fTarefas,fTarefas[Date created] = Max_Date)) +0
Taks open (abertas) =
VAR Max_Date= CALCULATE(MAX(fTarefas[Date created]),FILTER(ALLSELECTED(fTarefas),fTarefas[Tarefa - ID]=MAX(fTarefas[Tarefa - ID])))
RETURN
CALCULATE(DISTINCTCOUNT(fTarefas[Tarefa - ID]),fTarefas[Tarefa - Status] = "ABERTAS", FILTER(fTarefas,fTarefas[Date created] = Max_Date)) + 0
Anyone know what is wrong?
Solved! Go to Solution.
Hi @gbernardes
Can you provide some sample data with multiple task ids and expected result based on that? May a task have multiple status records on a date? Do you only need to consider the last status of a task on a selected date?
Based on my assumptions, you can first add two calculated columns in the table:
Date = DATEVALUE('Table'[Date updated])
Is Last record =
IF (
CALCULATE (
MAX ( 'Table'[Date updated] ),
ALLEXCEPT ( 'Table', 'Table'[task-ID], 'Table'[Date] )
) = 'Table'[Date updated],
1,
0
)
If a task has more than one status records on a date, it returns 1 for the last status row and 0 for earlier status rows.
Then create measures
Tasks Open = CALCULATE(DISTINCTCOUNT('Table'[task-ID]),FILTER('Table','Table'[Is Last record] = 1 && 'Table'[Status] = "Open"))+0
Tasks Done = CALCULATE(DISTINCTCOUNT('Table'[task-ID]),FILTER('Table','Table'[Is Last record] = 1 && 'Table'[Status] = "Done"))+0
Total Tasks = DISTINCTCOUNT('Table'[task-ID])
Put the new "Date" column in the slicer instead of "Date updated" column.
I have attached the pbix file. See if it meets your need.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @gbernardes
Can you provide some sample data with multiple task ids and expected result based on that? May a task have multiple status records on a date? Do you only need to consider the last status of a task on a selected date?
Based on my assumptions, you can first add two calculated columns in the table:
Date = DATEVALUE('Table'[Date updated])
Is Last record =
IF (
CALCULATE (
MAX ( 'Table'[Date updated] ),
ALLEXCEPT ( 'Table', 'Table'[task-ID], 'Table'[Date] )
) = 'Table'[Date updated],
1,
0
)
If a task has more than one status records on a date, it returns 1 for the last status row and 0 for earlier status rows.
Then create measures
Tasks Open = CALCULATE(DISTINCTCOUNT('Table'[task-ID]),FILTER('Table','Table'[Is Last record] = 1 && 'Table'[Status] = "Open"))+0
Tasks Done = CALCULATE(DISTINCTCOUNT('Table'[task-ID]),FILTER('Table','Table'[Is Last record] = 1 && 'Table'[Status] = "Done"))+0
Total Tasks = DISTINCTCOUNT('Table'[task-ID])
Put the new "Date" column in the slicer instead of "Date updated" column.
I have attached the pbix file. See if it meets your need.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |