Measure across two tables with several columns with many to many relationships

[Peter_]

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

[v-juanli-msft · Accepted 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

[MFelix]

Hi @Peter_

Believe that the best way is to create dimension tables with unique values for each common field then make the relationship between both tables and the dimension tables.

Don't forget to use the dimension table fields to make your visuals.

Regards
MFelix