Multiple relationships between 2 tables

[mgost]

Hi everyone,

I really need your help. I am stuck.

I need to calculate Amount by User in Document Number, but user can occur in any of three columns - First User, Second User or Third user. I cannot filter Document Number and Amount by User from User table, because I can't have ambigous relationship between table Delivery Header and User table. How can I make relationship between these tables?

or directly in measure if user appears in First User => sum this amount and document number etc...
I have lost one day and I can't figure it out...
Thanks!

[Anonymous]

Hi,

For the data model:

Can you merge your Delivery Order (Header) table into the Delivery Line Item (Detail) table? That would simplify your model.

If not, you can still use an active relationship on User 1, and inactive relationships on User 2 and User 3 against your User dim table.

Then, for the measures:

Maybe do the User count in your Delivery Order table as a calculated column

And for your question 1 :

That is just a simple summation:  SUM(DocDetail[Amount])

For question 2 - Check user name and sum:

SUM using USERELATIONSHIP

For Question 3 - If user count = 3...then divide by 3

You could use a summarized table and wrap that in a SUMX measure to get the result you want. (here i just made a table)

[mgost]

Thank you very much, it is on good track, but if i use it in table in visual i still get only documents shown for user where user is in first name column. In my model, here are shown 4 documents for the user and he has deliverd 7 documents(in 3 other documents he is in second column-second name). how can i show all 7 when i use filter "Users"

[Anonymous]

Hi,

To display and filter records like that, i think you need to change the approach to use a bridge table.  That will also make your dax measures more simple as you dont need to use "if" & "userelationship".

In your power query, create a new table from the User1, User2, User3 and Doc columns only then unpivot so you only have the two columns of Doc and User remaining.

In the data model, set the relationships so they are active and the DocHeader relationship is Bi-directional.

 

[Anonymous]

Hi @mgost ,

I'm assuming the data in the Delivery Order table looks like this:

If so, select the three User columns, right click and click the unpivot columns:

Then filter out Blank.

Relationship:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[mgost]

Thank you for your reply, but I would also use unpivot to manage these 3 columns-that is my backup solution. The problem is that my Delivery Order table using unpivot is growing in size. I want to keep it as it is, because data model is already big. Is there any way to use three relationships on delivery order from users? i tried with 2 inactive realtionships and of course, and with USERELATIONSHIP but it gives error because it is then ambigous relationship... 

[mgost]

this is my code, that doesn't work...

[ajohnso2]

Can you provide some sample data from your three tables that will show this issue you have?

[mgost]

Sorry I can't share company data, and don't have so much time to recreate everything with dummy data.
but here is detailed diagramm: