Forum Discussion
Count duplicate values using a DAX measure
Hi
I have data that looks like this:
| EmploymentID | PersonID |
| 1 | A |
| 2 | A |
| 3 | B |
| 4 | C |
| 5 | D |
| 6 | E |
| 7 | F |
| 8 | G |
| 9 | G |
| 10 | G |
I want to count how many employments each person has, which would result in something like the following:
| EmploymentID | PersonID | No. Of Employments |
| 1 | A | 2 |
| 2 | A | 2 |
| 3 | B | 1 |
| 4 | C | 1 |
| 5 | D | 1 |
| 6 | E | 1 |
| 7 | F | 1 |
| 8 | G | 3 |
| 9 | G | 3 |
| 10 | G | 3 |
The challenge is, I want the No. Of Employments value to be effected by filters, so I need it to be a measure, rather than a calculated column.
This is the formula I used to create a calculated column:
No. Of Employments = CALCULATE ( COUNT ( [PersonID] ), FILTER ( Dim_Employments, [PersonID] = EARLIER ( [PersonID] ) ) )
However, this does not work as a measure; it returns the following error message:
"EARLIER/EARLIEST refers to an earlier row context which doesn't exist."
Any thoughts?
Thanks!
Hi bullius,
Add you columns to the all except.
The new measure would be something like this:
Measure = CALCULATE( COUNT(Table1[PersonID]), ALLEXCEPT(Table1,Table1[PersonID],Table1[status]))
as you can see below the top half is raw data and measure without interacting with slicer, bottom part table is filter by status B
Regards,
MFelix
10 Replies
What do you mean to be affected by visual level filters?
What type of iteractions you want to have and expected result.
Regards
Mfelix
Hi Anonymous ,
The ALLEXCEPT function removes all context filters in the table except filters that have been applied to the specified columns. In this case when making the count it will only keep the ID and Status filter on the table.
In this case the calculation is picking up all users with the selected status B.
Regards,
MFelix
