cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gbernardes
Frequent Visitor

Distinctcount IDs by status and last date updated

Hello. I have a table that register status of tasks in the time. 

gbernardes_0-1652278315323.png

 

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?

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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
)

vjingzhang_0-1652685000842.png

 

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.

View solution in original post

1 REPLY 1
v-jingzhang
Community Support
Community Support

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
)

vjingzhang_0-1652685000842.png

 

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.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors