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

MFelix's avatar
MFelix
Super User
8 years ago

Hi bullius,

 

Use the following measure:

 

N.º Employments  =
CALCULATE ( COUNT ( Table1[PersonID] ), ALLEXCEPT ( Table1, Table1[PersonID] ) )

Regards,

MFelix

Anonymous's avatar
Anonymous
Not applicable
6 years ago

How All Except Function is working in that function

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    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