Need help summarizing row count

[faustusxanthis]

Hello!

It seems I'm out of options and I can't seem to find the right solution. 

My data is like this: 

Date	Person	Count Instance
1/1/2024	Person 1	2
1/1/2024	Person 1	2
1/1/2024	Person 2	1
2/5/2024	Person 1	1
2/7/2024	Person 2	1
2/5/2024	Person 3	1
9/1/2024	Person 4	1
10/6/2024	Person 2	2
10/6/2024	Person 2	2

The data for Date and Person are already present. I just need to create a DAX expression for a column (Count Instance)that calculates the instance of the combination of Date and Person (eg: Person 2 with date 10/6/2024 appeared 2 times, therefore the count should be 2). 

I tried to use SUMMARIZE but it's giving me error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Column = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Date]=EARLIER(Data[Date])&&Data[Person]=EARLIER(Data[Person])))

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Column = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Date]=EARLIER(Data[Date])&&Data[Person]=EARLIER(Data[Person])))

Hope this helps.

[lbendlin · Accepted solution]

Here is a better version

Count Instance =
CALCULATE (
    COUNTROWS ( Data ),
    TREATAS ( { ( [Date], [Person] ) }, Data[Date], Data[Person] )
)

 

Not using EARLIER (which is discouraged anyway) or variables.

[lbendlin]

No need to use DAX. The implicit measures can do that for you. Power BI automatically aggregates.

[faustusxanthis]

Hi @lbendlin, 

Yeah I know I can do that but I need it to show as another column in Data view. 

[lbendlin]