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
Highlighted
Super User
Super User

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



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

Proud to be a Datanaut!




View solution in original post

6 REPLIES 6
Super User
Super User

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



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
Super User

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



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?

Highlighted
Super User
Super User

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



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

Proud to be a Datanaut!




View solution in original post

bullius Member
Member

Re: Count duplicate values using a DAX measure

Thanks again!

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 142 members 1,677 guests
Please welcome our newest community members: