Forum Discussion
Multiple relationships between 2 tables
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)
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"
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.
