Count duplicate rows in Power BI using DAX

27 January 2022 ,

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]
)
One thought on “Count duplicate rows in Power BI using DAX”
  1. Thank you for the tip! Just an FYI, the code above for the multiple column filter has too many closing parameters. There is a missing EARLIER( reference when checking the second column ‘Surname’.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.