Latest Enterprise DNA Initiatives

Issues Creating Relationships due to duplicate values

Data Model
#1

Hi Fellow Power BI Users -

I have just been given a task of creating reports for a new database and have run into an issue. It may be an easy one but I am not understanding what is wrong. In my data model i have a sales orders table and a stock table which describes our products etc. However, when i try to create a relationship between the stock codes in each table i am given an error as seen in the photo. I have checked and removed for duplicates as well as nulls and blanks yet still get the error. Any idea what could be causing this issue? I didn’t want to do a bad work around since its a pretty base/ major thing to set up right.

Error -

image
image.png696×275 24.2 KB

Part of my data model

image
image.png745×1040 93.7 KB

Data - the stock codes do match just maybe not many in this photo shown

image
image.png2041×1547 979 KB

Cheers for any suggestions,

#2

Ok, I don’t think you should be creating a relationship like this at all.

What you should be doing instead is creating a products lookup table, with each unique product value. Then you create a one to many relationship down to each of those tables.

You’ll see that I detail an example of how the model should be in this tutorial

Here’s a snapshot of what I mean.

image
image.jpg1096×780 84.4 KB

To create this product lookup table from your current tables you’ll want to step through these ideas in the query editor.

  1. Reference the stock code table
  2. Delete any unrequired columns
  3. Remove any duplicates from the index column to make double sure nothing is repeated
  4. Rename this the products tables

This should do it I think

Chrs
Sam

#3

Appreciate that Sam, will give it a try.

#4

Hi Sam,

Still having trouble with this, even though when cleaning data i removed all duplicates and blanks, i wrote a quick formula to check if indeed there was no duplicates by doing a count against stock codes (Primary Key) as well as just checking for count vs distinct count and the numbers were different.

As you can see applying the formula in the photo it gives me numerous stock codes that appear more then once. However when i look into the data without that count there is no duplicate of it that i can find what so ever. what would be causing this to counting duplicates? especially since i have said to remove all duplicates? Even when deploying your technique as described above, it still cant get around this duplicate issue.

Formula:
image

Result of Formula:

image
image.png652×1424 121 KB

Data Table as is - nothing applied (Can see there is no duplicates - this is the exact same data that the formula above was applied to but with no formula against it)

image
image.jpg1504×1633 151 KB

Kind Regards,

Cheers

#5

One things that can cause an issue here is if some of your columns have lower case versus upper case.

Can you actually just use COUNTROWS( tablename) as your formula. That’s all you need to check this. (I do this quite often and have had this happen occassionally)

Also double check that you are in fact checking this with a table at the highest level of granularity. Is the table definitely at the level of context?

Another tip. In the query editor, maybe test this and filter the table for only one of these stockcodes. That will make auditing this way easier. If you find the issue for one, you’ll find the general problem. I find this all the time.

#6

Thanks Sam,

That has worked now

Appreciate it.