Finding Duplicates in DAX
A prerequisite for creating a relationship in Tabular/Power Pivot is to have a primary key column in the table on the One side of the relationship. This column must have unique values. If it doesn’t, the relationship won’t get created and you’ll get an error that the both tables have duplicate keys. If you have a relatively large table, it might be difficult to find the duplicates.
ID | Column1 |
1 | Foo |
2 | Foo |
1 | Foo |
However, given the above table design, you can add a simple calculated column to the table to return the count of duplicates for column ID using the following DAX formula
=CALCULATE (COUNTROWS(), ALLEXCEPT(Table1, Table1[ID]))
This expression uses the COUNTROWS() function to count the number of rows of Table1. Coupled with the CALCULATE function, this expression will be resolved in the context of every row. To ignore the column that you want to count on (ID in this case), you need to exclude it from the context, so that the row counting happens across the entire table for each ID value. Once the column is created, you can filter on it in the Data View to find out the duplicate rows with values 2, 3, etc.
