Forum Discussion
Sum data with multi relationships
Whenever you see column names with xxx1, xxx2, xxx3 etc. the spider sense should start tingling. Having a set of non-active relationships to deal with these columns also sets alarms off.
The Company columns should be unpivoted in Power Query to provide a column holding the Company name (company1, company2...) and a column holding the Company ID (1001, 1002...)
A single relationship can then be created between ID in Organisation and Company ID in the revamped Deal table.
The challenge that remains is to prevent double-counting of the Value field when linking to a User.
One way to do this would be : create a measure for the average(Value) and then use that in another measure like
Norm Value = SUMX(DISTINCT(Deal[ID]), [Avg Value])
This works out the average deal value based on a unique identifier in the Deal table (so prevents double counting)
no, only that that relationship.
Bi-directional is very dangerous to be abusive if you don't fully understand how it works.
for this case, only that relationship is required to perform the task you are looking for.
you may refer to this article for better understanding how it works:
https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/
- Yes, you can use Expanded Table as well.
Calculate(sum(value), bridgetable)
Please mark as solution to close the topic.