Forum Discussion

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...

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

Super User

8 years ago

Use the following measure:

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

Regards,

MFelix

bullius
Helper V
8 years ago
Thanks MFelix,

I created a matrix with the measure in the values bucket, but it does not seem to be affected by visual level filters...
- MFelix
  Super User
  8 years ago
  What do you mean to be affected by visual level filters?
  
  What type of iteractions you want to have and expected result.
  
  Regards
  Mfelix
  - bullius
    Helper V
    8 years ago
    My data also includes other fields like Status (Active/Inactive).
    
    An example of the functionality I want would be:
    
    I want to be able to filter the matrix to only include active emplyments and the No. of Employments per PersonID to change.
    
    Make sense?
Anonymous
Not applicable
6 years ago
How All Except Function is working in that function
- 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

Related content