Create a count of records per ID

[Anonymous]

Hi All, 

Im looking to accomplish something very simple. Here's a sample:

ID	Date
1	8/1/2021
1	7/1/2021
2	7/1/2021
3	8/1/2021
3	7/1/2021
4	8/1/2021

Im essentially looking to get an idea of employees that "exited", or do not have a record for a selected date. I thought the best way to do this would be to get counts of dates per employee. 

Desired Output:

ID	Count
1	2
2	1
3	2
4	1

Selected Value: 8/1/2021

Output:

ID	Exists?
1	1
2	0
3	1
4	1

Does anyone have any experience with something like this? Any input is appreciated!

[Anonymous · Accepted solution]

Hi @Anonymous ,

Method1: You could simply change the summarization type as Count like this:

But it will not show IDs whose date is not selected such as ID=2.

In this case, I'd suggest you add a new table to get all IDs:

IDs = VALUES('Table'[ID])

And then create a measure:

Measure =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[Date]
            IN ALLSELECTED ( 'Table'[Date] )
                && 'Table'[ID] = MAX ( 'IDs'[ID] )
    )
) + 0

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@Anonymous 

i guess if the real situation is more complex than your sample date.

do you want to show 0 for all ID when select 2021/7/1?

do you have IDs which have more than two rows?

e.g 

ID date

1 2021/1/1  enter?

1 2021/2/1  exit?

1 2021/3/1 enter?

1 2021/4/1 exit?

[Anonymous]

@ryan_mayu Yes, there will be multiple rows for the same ID as more files (dates) are added. Theoretically yes, when an ID is showing up for the first time, were considering that an "enter", and when an id no longer shows up for the month selected and exists for the previous month, we consider that an "exit"

[ryan_mayu]

@Anonymous 

why ID 4 count 1? Since i only see one record

[Anonymous]

@ryan_mayu It only has one date associated with it. That is technically only an intermediary check. I believe i have solved this, but am looking for help on the next part, seen here:

https://community.powerbi.com/t5/Desktop/Need-Help-Fixing-Measure-Summing-a-count/m-p/1997873