Count duplicate rows in Power BI using DAX

If you’re working with data in Power BI and would like to count how many duplicates there are, the below example will help you.

Let’s start with the table structure. For this example, lets says we want to see how many people have the same first name, and the data looks like this:

EmployeeIDFirstNameSurname
1JohnPrice
2SarahWilliams
3WilliamSmith
4JeffBridges
5RichardWalsh
6KateJones
7SarahParker
8WilliamWallace

Now lets create a new column and create a formula to count how many people share the same first name.

1) Switch to the data view

2) Make sure you have your table selected, in my case, I only have one

3) Select ‘New column’

You should then see the below

4) Where it says ‘Column’, this is what you want the new column to be called. Change this to whatever you want your new column to be called, I’ll go with ‘SharesTheSameFirstName’ for mine.

5) Now let’s make the query itself (replace Query1 with the name of your table). The unique row id for this example is EmployeeID, so we will count the rows with unique id’s that have the same FirstName.

Here’s a simple example to get you started:

SharesTheSameFirstName = 
COUNTX(
    FILTER( Query1, EARLIER(Query1[FirstName])=Query1[FirstName]),
    Query1[EmployeeID]
)

6) When you have your code as you want, click the tick, and your data should be like this:

EmployeeIDFirstNameSurnameSharesTheSameFirstName
1JohnPrice1
2SarahWilliams2
3WilliamSmith2
4JeffBridges1
5RichardWalsh1
6KateJones1
7SarahParker2
8WilliamWallace2

You can filter on multiple colums too, so you could alter the code to check for people with the same first name and surname? An example of this approach would be:

SharesSameName = 
COUNTX(
    FILTER( Query1, EARLIER(Query1[FirstName])=Query1[FirstName]) && Query1[Surname])=Query1[Surname]),
    Query1[EmployeeID]
)

Be the first to comment

Leave a Reply

Your email address will not be published.


*