cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bullius Member
Member

Count duplicate values using a DAX measure

Hi

 

I have data that looks like this:

 

EmploymentIDPersonID
1A
2A
3B
4C
5D
6E
7F
8G
9G
10G

 

I want to count how many employments each person has, which would result in something like the following:

 

EmploymentIDPersonIDNo. Of Employments
1A2
2A2
3B1
4C1
5D1
6E1
7F1
8G3
9G3
10G3

 

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Count duplicate values using a DAX measure

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

 

allexcept.png

Regards,

MFelix


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

8 REPLIES 8
Super User III
Super User III

Re: Count duplicate values using a DAX measure

Hi @bullius,

 

Use the following measure:

 

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

Regards,

MFelix


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




bullius Member
Member

Re: Count duplicate values using a DAX measure

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

Super User III
Super User III

Re: Count duplicate values using a DAX measure

What do  you mean to be affected by visual level filters?

 

What type of iteractions you want to have and expected result.

 

Regards

Mfelix


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




bullius Member
Member

Re: Count duplicate values using a DAX measure

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?

Super User III
Super User III

Re: Count duplicate values using a DAX measure

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

 

allexcept.png

Regards,

MFelix


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Highlighted
bullius Member
Member

Re: Count duplicate values using a DAX measure

Thanks again!

NitinVerma New Member
New Member

Re: Count duplicate values using a DAX measure

How All Except Function is working in that function

Super User III
Super User III

Re: Count duplicate values using a DAX measure

Hi @NitinVerma ,

 

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 


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors