Multiple Relationship between same Tables

[megh_a]

Hello,

I am developing a database in PowerPivot and I am wondering how to create many relationships between the same 2 tables. All relationships must be active.

Let me give you a DUMMY example: let's say that the database has 2 tables, the Employee table and Manager table (below images).

Because I have 2 manager fields in the employee table, I need to create 2 links between the employee and manager tables (see below images).

Right now, one of the links is inactive...
Is there a way in PowerPivot to create 2 active links like that ?

Thanks for anyone who can help !

P.S.: I have Power Pivot version 11.0.3000.0 on Excel 2010 on Windows 7

 

[JNM]

I have done something similar with a SQL backend... If you go to the table you want to duplicate, then select Table Design/Table properties and switch to "Query Editor". Copy the resulting code and paste it into Word. Cancel from this window. Go to Home/Existing Connections/Open, select the Write a query... option. Copy and paste the code from the Word document, giving the query a name different from the original table.

 

[megh_a]

Thanks JNM for your reply.

Unfortunately, my "Table Property" button is grayed out because I am using "Linked Tables"... do you have another idea of how I can use your method ? Or should I not use Linked-Tables ?

 

[JNM]

Let's assume Manager table 1 has two columns - Manager code and Manager name, there are six rows to this table and it's on Sheet 1 starting at A2. Add a second Manager table starting at D2, down six rows. and then in Cell D3, put the formula =A3 and in E3 put the formula =B3. If this is in Excel 2010 or 2013, it will then autofill the formula keeping the Manager info in sync. If you add a new row to Manager 1, you just need to add a new row to Manager 2.

 

[megh_a]

Hi JNM,

the formulas are a good idea ! I also used some "LookupValue" to replace relationships when the data to retrieve was very simple (changing an ID for a name).

Problem solved, thanks !