Forum Discussion
Use multiple relationships in a measure
Hello Everyone!
I've been at this for a while now and have been researching to no avail!
I have two tables with 3 columns that need to be joined (Date, Code and Company]. The desired outcome would be for the MM Measure (file here) to filter using the same date slicer under the correct Company and Code - so far it's returning grand totals.
So far I have one active relationship and two inactive ones. The measure looks like this:
MM Measure = CALCULATE(SUM(Sheet2[MM Count]),
USERELATIONSHIP(Sheet1[Code],Sheet2[Code]),
USERELATIONSHIP(Sheet1[Company],Sheet2[company_id]),
USERELATIONSHIP(Sheet1[Date],Sheet2[date])
) This does not seem to work.
The SQL equivilent would be something like this:
SELECT sheet1.company,
sheet1.code,
sheet1.date,
sheet1.adjdate,
sheet1.net,
sheet1.count,
sheet2.[mm count]
FROM sheet1
LEFT OUTER JOIN sheet2
ON 1 = 1
AND sheet1.company = sheet2.company_id
AND sheet1.code = sheet2.code
AND sheet1.adjdate = sheet2.date
hi, Anonymous
For your case, you to refer to this way:
Create a combined value column for each table, then use them to create a relationship.
for example:
for sheet1 table
combined = Sheet1[Company]&Sheet1[Code]&Sheet1[Date]
for sheet2 table
combined = Sheet2[company_id]&Sheet2[Code]&Sheet2[date]
Then create a relationship as below
Best Regards,
Lin
2 Replies
v-lili6-msftCommunity Support
hi, Anonymous
For your case, you to refer to this way:
Create a combined value column for each table, then use them to create a relationship.
for example:
for sheet1 table
combined = Sheet1[Company]&Sheet1[Code]&Sheet1[Date]
for sheet2 table
combined = Sheet2[company_id]&Sheet2[Code]&Sheet2[date]
Then create a relationship as below
Best Regards,
Lin