Forum Discussion
Multi Variable N:N Relationships?
Hey all,
I am a little stumped on a task I've been asked to do. I have two tables that have three identical variables I need to compare against each other: budget vs spend.
The variables in each table that need comparison are type of spend, quarter, and world region. Each quarter every region gets 7 different types of spend to budget for. This means I have 112 different budgets all of which will duplicate one of these fields at least 4 times.
For the expenses I've managed to rig it up so that those same 3 fields will match to the budget table. I can create in the BI report two graphs: one for budget and one for spend. I can't get them to connect because Power BI complains that it can only handle ONE relationship of an N:N variety to use to filter each other.
Is there any way to get around this? As it is the report is pretty useless when you can't compare side by side expenses and budget. I need to be able to drill down by type and by individual quarter.
Hi bkirkey,
Create a table with distinct type value from those tables, then create relationship with 'both' cross filter direction.
Link Table = DISTINCT(UNION(VALUES(Sheet2[Type]),VALUES(Sheet3[ Type])))
Reuslt:
Regards,
Xiaoxin Sheng
4 Replies
Very difficult to picture this with just words, can you post some sample data?
Sorry, here's the part of the data I am talking about with the extra stuff removed. This is what comes in for expenses:
Actual
Region
Type
Quarter
225
EMEA
Recruiting
Q1
1106
Asia
T&E
Q2
322
EMEA
T&E
Q3
2939
EMEA
Computers
Q4
1596
Management
T&E
Q1
2872
EMEA
Morale
Q2
4656
EMEA
T&E
Q3
1977
EMEA
T&E
Q4
1885
Americas
Employee Dev
Q1
1738
EMEA
Auto
Q2
1772
EMEA
Dues & Subscriptions
Q3
And this is the budget, arranged by Type, Amount, Region, and Quarter.
r Auto 127849 Americas Q1 Computers 20142 Americas Q1 Employee Dev 76731 Americas Q1 Morale 133345 Americas Q1 Recruiting 83896 Americas Q1 T&E 51930 Americas Q1 Dues & Subscriptions 138190 Americas Q1 Auto 132806 Asia Q2 Computers 33870 Asia Q2 Employee Dev 12697 Asia Q2 Morale 44866 Asia Q2 Recruiting 101465 Asia Q2 T&E 55889 Asia Q2 Dues & Subscriptions 140635 Asia Q2 I need a chart comparing those side by side on a per region basis.
So it would have the $$ on the Y axis and each region as an X axis, with the legend being each of the "Types." I also need the ability to filter the data by Quarter, so Q1, 2, 3, 4.
Note that for budgets Asia would also have a Q1 like the americas but I hit a character limit :). So you'd have an America, Asia, EMEA, and Mgmt for all four quarters with a seperate type budget for each quarter.For the expenses there will be thousands of them coming in.
Thanks!
Hi bkirkey,
Create a table with distinct type value from those tables, then create relationship with 'both' cross filter direction.
Link Table = DISTINCT(UNION(VALUES(Sheet2[Type]),VALUES(Sheet3[ Type])))
Reuslt:
Regards,
Xiaoxin Sheng
HI bkirkey,
Please provide more detailed information to help us clearly know your scenario.
BTW, current power bi not support to direct create M:M relationship, you should add a bridge table to link them and convert to M:1 relationship.
After above steps, if you want to use both side data in one visual at same time, you should summary one side, otherwise it will get error.
Regards,
Xiaoxin Sheng
