Forum Discussion
Need help summarizing row count
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."
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.
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.
5 Replies
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.
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.
No need to use DAX. The implicit measures can do that for you. Power BI automatically aggregates.
Hi lbendlin,
Yeah I know I can do that but I need it to show as another column in Data view.
