PowerBI doesn't allow one-to-many-relationship

[Anonymous]

Hello all,

I have a problem:

I got a table which contains duplicated values (a duplicated value means one order contains of more than 1 item). Anyway, at one side, I need the duplicated values to count the gross sold items, at the other hand I also need the number of orders to count the weighted value of an order, hence, net to get rid of the duplicated values.

Therefore, I created a table which contains all columns from the original table, but gets rid of the duplicatons. For the creation of the table I used: 

Distincttable = DISTINCT(SELECTCOLUMNS('Table1',"Sales Doc.",'Table1'[Sales Doc.]))

I named the column of the new table "Sales Doc.", same as in the original table so I can create a relationship with the original table and use it in my visuals. This is because the original displays for every position the gross order value. That means if 1 position contains 20 items, whichs gross value is 1000$, it displays me 1000$ 20 times.

However, I can't create a one-to-many relationship from the new to the original table now as PowerBI tells me this is a many:many-relationship.

But by using the Formula for creation of the new table I made sure that it contained each value only once, so how can it not be a one:many-relationship?

[PhilipTreacy]

Hi @Anonymous 

Can you please provide soem data to work with?  Without using your data it's a bit tricky!

If PBI is telling you that it's a many-to-many relationship then I'd say it is so we're missing something here. Without your data it's hard to help.

Regards

Phil

[Anonymous]

Hi Phil, 

I'm sorry but I cant provide the dataset as it's confidential. And like said I've now figured out why this error occured (blank values where there shouldn't have been some) so now I got problems with the measure...:/

[PaulDBrown · Accepted solution]

@Anonymous 

I've just tried your table syntax in a dummy dataset and it works (no duplicated values). This can occur if the dataset includes null values or blank values for the field used in the relationship.

Can you try two things:

1) create the table using VALUES('Table1'[Sales Doc.])

2) creat another table using DISTINCT('Table1'[Sales Doc.])

One of these, (I thin the VALUES option) will include "blank" as a row if the dataset contains blank rows. 

Try if either of these works in a one-to-many relationship.

[Anonymous]

Indeed the issue was the blank values. I changed them using "replace values" in PowerQuery and it ended up working and creating the path for a follow up problem 🙂

[Anonymous]

Well like mentioned below, it didn't work as intended, however, I've solved that issue by replacing all blanks in the original tables (which definitely should not be there!) by "0" via PowerQuery. After, I could establish a 1:many-relationship.

Anyway, the value output is still wrong. So, I'm using the measurement:

Net value per Order =

DIVIDE(

    SUM('UNION'[Net value]),

    COUNT('Distincttable'[Sales Doc.])

As the Distincttable now has a relationship with the UNION-table (don't get confused, I used to the refer to the UNION-table as Table1) I hoped that it would filter the values of Net Value in a way that it would only count the net value once per Sales Doc.

I mean it somehow works (the value given out now is about 10 times higher as the actual value that I determined to check if it works; the value given out without using this method at the other hand is approx. 100times higher), but that doesn't help much as it still doesn't work.

EDIT: Solution would be to add a column in the distincttable, where the net value is allocated to the respective Sales Document (like, loading the data from the Net value data into the distincttable), then I could use the Net Value in the distincttable to solve my issue, but I don't know how to add a column which does that

So did I just end up using the wrong measure here?

[Anonymous]

Hi @PaulDBrown ,

First of all thanks for your kind support!

However, I tried both options and unfortunately neither of them worked...

1) I created the VALUES-Table, but it told me a circular dependancy was created

2) I Created the second distinct table, that filters the first one, but when I tried to link that to the table that I want to be filtered, it told me, "that this cardinality isn't valid for this relationship". This, however accounts for every relationship except the "many-to-me"-option so that doesn't work either and now I'm kind of desperate...

I also can't override thos errors as they disable the "OK"-button...