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.
Is there a way in Power Bi to create a measure that is based on values from two tables and across multiple columns common across the tables where several columns have many to many relationships between two tables?
I can workaround this by union-ing the tables into a single table beforehand, but I wonder if there's a way to keep these tables separate and let Power Bi handle it. Right now, Power Bi complains if I try to make multiple many-to-many relationships active between two tables.
Example:
Tables:
Income, Outcome
Columns:
Income = { SocialID, Gender, Income, City, Country, Workplace}
Outcome = { SocialID, Gender, Outcome, City, Country, PlaceOfExpenditure}
* note the set of people (~SocialID) in both is not all the same.
Measures:
Outcome to Income ratio
Number of People
Chart:
Graphs the measure across city or city + Gender, etc..
Thanks,
Peter
Solved! Go to Solution.
Hi @Peter_
You could consider MFelix's suggestion,
You could select "New Table" and enter the formula below to create a table with unique SocialID:
new table1 = UNION ( EXCEPT ( SUMMARIZE ( income, income[SocialID] ), SUMMARIZE ( outcome, outcome[SocialID] ) ), SUMMARIZE ( outcome, outcome[SocialID] ) )
Then create relationships between this new table1 with "income" and "outcome" tables
new table1(one to many) "income" new table1(one to many) "outcome"
If you have any problem, please let me know.
Best Regards
Maggie
Hi @Peter_
You could consider MFelix's suggestion,
You could select "New Table" and enter the formula below to create a table with unique SocialID:
new table1 = UNION ( EXCEPT ( SUMMARIZE ( income, income[SocialID] ), SUMMARIZE ( outcome, outcome[SocialID] ) ), SUMMARIZE ( outcome, outcome[SocialID] ) )
Then create relationships between this new table1 with "income" and "outcome" tables
new table1(one to many) "income" new table1(one to many) "outcome"
If you have any problem, please let me know.
Best Regards
Maggie
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |