Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Best Regards,
Lin
Worked like a charm! thank you
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |