Use multiple relationships in a measure

[Anonymous]

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 

[Anonymous]

Worked like a charm! thank you

[v-lili6-msft · Accepted solution]

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

https://community.powerbi.com/t5/Desktop/Cannot-create-multiples-column-relationship-between-two-tables/td-p/48261

Best Regards,

Lin