Forum Discussion

bullius's avatar
bullius
Helper V
8 years ago
Solved

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 eac...
Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi MFelix 

 

i'm trying to do something similar to this. Would you know how to create the same output but without having the Person ID displayed on the table? i would like a dax expression that automatically looksup the corresponding personID related to the ID and returns a count. so final table would look like

 

ID     Count

1       2

2       2

3       1

4       1

5       1

6       1 

7       1

8       3

9       3

10     3

with the above DAX expression, i am getting a 7 in every row. any help appreciated, thanks

MFelix's avatar
MFelix
Super User
5 years ago

Hi Anonymous ,

 

What do you mean by not having the person ID displayed in the table?

 

The calculation of measures is based on context so if you don't have in this case the person ID the result will be different.

You also mentioned that you have 7 in all rows, what is the value for each row? is it another column on your data?

 

Dou you want to have a table only with the values  on the count measure?