Multiple relations between two tables in Power Pivot

MrNymm

New Member
Joined
Oct 5, 2016
Messages
5
Hi,

My Power Pivot data model consists of a number of linked Excel tables, of which tblEvents is the main one. It has the following columns:


  • Date
  • Location, related to the column "LocationName" in tblLocations
  • EventType, related to the column "EventType" in tblEventTypes
  • Client, related to the column "ClientName" in tblClients
  • AudienceSize, a numeric value
  • Lead, related to the column "EmployeeName" in tblEmployees
  • Speaker1, should be related to the column "EmployeeName" in tblEmployees
  • Speaker2, should be related to the column "EmployeeName" in tblEmployees
  • Speaker3, should be related to the column "EmployeeName" in tblEmployees
  • Backup, should be related to the column "EmployeeName" in tblEmployees


As you can see, multiple columns of the main table ought to be related to the same column in the support table tblEmployees, but this isn't allowed in Power Pivot. One potential fix is to add this support table multiple times to the data model, once for every link. However, the reason I am using linked tables is because new rows are regularly added to tblEmployees. This solution is therefore not feasible.

As an alternative, I've been trying to create a measure in tblEmployees which counts, for each employee, the number of rows in the main table (tblEvents) where that employee was involved in any role (Lead, Speaker1, Speaker2, Speaker3, Backup), while preserving pre-existing filter context (e.g. period, event type, client, …).

My attempts so far have all failed miserably. I've tried to modify similar queries I found online, but it seems I simply don't "get" it yet. Any help would be greatly appreciated!
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,260
How about you unpivot the last 5 columns to create a “speaker type” and employee name. Then join the single employee name to your employee table.
 

MrNymm

New Member
Joined
Oct 5, 2016
Messages
5
Thank for your reply!

If I understand your suggestion correctly, a single event could then take up to five rows in the mail table, one for every role. Unfortunately, that would mess up quite a few other measures, which were built on the premise that one row equals one event...
 

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,260
Yes, that's what I mean. Unfortunately this just the way things work out sometimes. You need to decided if you will stick with the 'old' design so you don't have to make changes to work already done or go back and make changes so you can move forward. One of the most common comments I get from people with some experience with Power BI that then come to my Online Power BI Training is that they will now go back and start again armed with their new knowledge.
 
Last edited:

MrNymm

New Member
Joined
Oct 5, 2016
Messages
5

ADVERTISEMENT

Thank you for the advice.

I went back with this to the business team, but they are reluctant to change to having to input multiple rows for a single event. I will keep looking for a suitable DAX formula instead.
 

peter789

Board Regular
Joined
Nov 20, 2016
Messages
129
I hope my advice is not out of place considering the illustrious company on this thread but I don't think your team needs to input multiple rows. Remember Power Query is your friend when it comes to transforming your tables into a form that the data model can handle with its limitation on relationships.

Peter
 

MrNymm

New Member
Joined
Oct 5, 2016
Messages
5
Thanks for the clarification!

I’ve used Power Query to split the data of the tblEvents table into two seperate tables in the data model:

- the first table is a copy of tblEvents, with the staff columns (Lead, ...) removed and an index column added.
- the second table is a bridge table between tblEvents and tblEmployees. It is the result of unpivoting the five staff columns, and contains a Role column (“Lead”, ...), an EmployeeName column, and the same index column as above. Both index columns are linked in the data model, and so are the EmployeeName columns in this table and tblEmployees.

The bridge table has, in other words, two many to one relationships, one with the index in the copy of tblEvents, and one with EmployeeName in tblEmployees.

It’s a tad slow, and perhaps not the ideal solution, but it gets the job done. Thank you both for the advice.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,606
Messages
5,625,767
Members
416,136
Latest member
senthil_sk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top