3 Ways to Find Duplicate Values in Dimension Tables

While creating our beloved One-to-Many relationship you may face an error if the dimension table has duplicate values. In this post, I will talk about 3 ways to find duplicates in your dimension tables in Power BI.

 

Video – Find Duplicates in Dimension Tables

 

One-to-Many relationship between the fact and the dimension table requires unique values in the related column.

 

For example consider this attempt to make a One-to-Many relationship between the Sales and Product Table. Power BI throws a notification that the Product Table has duplicate values in the Product Code Column.

 

Let’s took a look at 3 ways to identify duplicate values in dimension table.

 

Method 1 – Create a Column in the Dimension Table to find Duplicate Values

I’ll create a new column in the products table with the following DAX (very similar to writing a COUNTIF function in Excel)

Check =
COUNTROWS (
    FILTER (
        Products,
        Products[Product Code] = EARLIER ( Products[Products Code] )
    )
)

You can also omit the Earlier function, and declare a variable instead. Refer to the code below:

Check =
VAR CurrProd = Products[Products Code]
RETURN
COUNTROWS(
    FILTER(
        Products,
        Products[Product's Code] = CurrProd
    )
)

1. In the resulting Products table I just apply filter on the check column and retain all the values > 1.
2. A value > 1 will be a duplicate.

 

Creating a column isn’t ideal if you are working with a very large Products table. Let’s have a look at another way to find duplicates.

 

Method 2 – Create a Visual to find Duplicate Values

Create a table visual and drag the Product code column to the values twice

Change the summarization of the second Product Code to Count. 

 

Now, apply the filter on Count of Product Code > 1.

And you only get to see the Duplicated Product Codes.

 

Method 3 – Write a Query either in DAX Studio or Tabular Editor to find Duplicate Values

Launch the instance of DAX Studio and write the following query.

EVALUATE
FILTER(
    'Products',
    CALCULATE(COUNTA('Products'[Product Code])) > 1
)

The query returns a table which appear duplicated in the Products Table.

 

 

---

 

More on DAX / PowerBI

Data Modeling makes DAX easy!

Some Interesting Power BI Tricks

Sum of Max Values in Power BI

Display Table or Matrix Until the Selected Date

Summing Visible Values of a Table in Power BI

Debugging DAX using CONCATENATEX