Measure that will sum how many times an Item ID appeared in one day

[Anonymous]

I have the following table:

Item ID	Date
6	01/01/2020
6	01/01/2020
7	01/01/2020
7	01/01/2020
7	01/01/2020
5	02/02/2020
6	02/02/2020
6	02/02/2020
6	02/02/2020
7	02/02/2020

I need to create a measure that will sum how many times an Item ID appeared in one day so I can have a table like this:

Date	Item ID	Instances
01/01/2020	6	2
01/01/2020	7	3
02/02/2020	5	1
02/02/2020	6	3
02/02/2020	7	1

Thanks

[V-lianl-msft]

Hi @Anonymous ,

Create a measure like this:

Measure = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Date],'Table'[Item]))

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

[amitchandak · Accepted solution]

@Anonymous , That you can have a simple count measure

count(Table[Item ID])

But you need a sum of distinct ID on a date, you need like this

sumx(values(Table[Date]), distinctcount(Table[Item ID]))

[mahoneypat]

You should be able to make a table visual that has Date and Item ID, and then add either column again but with an aggregation of count.  Or you can make a measure like ID Count = COUNT(Table[Item ID]) and add that.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat