Multi Variable N:N Relationships?

[bkirkey]

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.

[v-shex-msft]

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

[Greg_Deckler]

Very difficult to picture this with just words, can you post some sample data?

[bkirkey]

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!

[v-shex-msft · Accepted solution]

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