Multiple relations between two tables in Power Pivot

[MrNymm]

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!

 

[Matt Allington]

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]

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]

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.

 

[MrNymm]

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]

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]

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.