Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
bkirkey
New Member

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.

1 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])))

13.PNG

 

Reuslt:

 

14.PNG

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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
Auto127849AmericasQ1
Computers20142AmericasQ1
Employee Dev76731AmericasQ1
Morale133345AmericasQ1
Recruiting83896AmericasQ1
T&E51930AmericasQ1
Dues & Subscriptions138190AmericasQ1
Auto132806AsiaQ2
Computers33870AsiaQ2
Employee Dev12697AsiaQ2
Morale44866AsiaQ2
Recruiting101465AsiaQ2
T&E55889AsiaQ2
Dues & Subscriptions140635AsiaQ2

 

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])))

13.PNG

 

Reuslt:

 

14.PNG

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.