Problems with Relationships in 2 tables (originally excel files).....

Question

I am pretty new to the PowerPivot world and I have a little problem.  I have 2 excel files.  I have imported both files into PowerPivot and a relationship was established.  The 2 data files are titled Emails and ExecBu and I am trying to retreive the emails of the Execs.  The data is set up is as follows:

 

       Emails                                                                                     ExecBu

PERNR                      Email                                                                PERNR                  Name

 1001                      [e-mail]                                      1111                       Ed Green

 1002                      [e-mail]                                        1222                       Mike Smith

 1111                      [e-mail]                                      1333                       Eric Small

 1222                      [e-mail] 

 1333                      [e-mail]

 1334                       [e-mail]

 1336                       [e-mail]

 1385                       [e-mail]     

I had to manually create a relationship using PERNR and I did get the relaionship icon on both PERNR columns from both tables.  When I run a flat pivot table I keep getting the wrong result.  I place the Names in the row labels and when I put the email under the row label I get the entire listing of emails as opposed to only seeing the emails of the Executives.  All names and email addresses were made up for this exercise. Can someone assist me ?

Answer 1

You need to add a measure to your pivot table. For example, drag anything from your emails table (assuming relationship is from Emails to ExecBu) to your measure box in field list and it'll fix your problem.

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 2

Thank you very much.   I had not thought about using a measure to correct my expected results.  Hopefully this will resolve my issue.

Answer 3

When I put a measure on my pivot I had an error icon next to the measure and I didn't get any results.  I tried to use the FILTER measure and I got another error.  What DAX measure would anyone recommend that I use?

Answer 4

My data into Power Pivot is as follows:

A.

       Emails                                                                                     ExecBu

PERNR                      Email                                                                PERNR                  Name

 1001                      [e-mail]                                      1111                       Ed Green

 1002                      [e-mail]                                        1222                       Mike Smith

 1111                      [e-mail]                                      1333                       Eric Small

 1222                      [e-mail] 

 1333                      [e-mail]

 1334                       [e-mail]

 1336                       [e-mail]

 1385                       [e-mail]     

I had to manually create a relationship using PERNR and I did get the relaionship icon on both PERNR columns from both tables.  When I run a flat pivot table I keep getting the wrong result.  I place the Names in the row labels and when I put the email under the row label I get the entire listing of emails as opposed to only seeing the emails of the Executives.  All names and email addresses were made up for this exercise.

B.

I tried to put a measure onto the pivot and I got an error.  I've tried to use the SEARCH, FILTER and the FIND DAX measures.  Maybe my syntax is all wrong.  Does anyone know the formula that I could use to get a listing of the emails for the executives in my example?

Answer 5

I tested by just dragging PERNR to the measure field (Count of PERNR) and it's displayed correctly. You don't even need any DAX.. What does your error say?

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 6

If I understand what you want, here is how you can get the emails related to the user list.  I just tried this with similar data.

1. Make sure that the relationship is using the Email listing as the lookup side (Related Lookup Table).

2. You can then add the following DAX to lookup the email for each Exec in the ExecBu table by adding a column with the following syntax: =RELATED(Emails[Email]).  This will add the email address to the ExecBu table and you will be able to use it going forward.

Let me know if you have further questions on this.

---

Regards, Steve @dataonwheels

Answer 7

I tried to test, like you suggested and I am still not getting the answer that I am loking for.  My error exists when I added a measure to the pivot table. The formula that I used to create the measure was =RELATED([Email]) and an error insued stating that 'Function RELATED expects a fully qualified column reference as its argument'. 

Answer 8

Are you suggesting that I add that formula to the Pivot Table or add to the column in the Power Pivot screen?  I have tried to add that formula to the pivot table and I got a prompt stating that 'Function RELATED expects a fully qualified column reference as its argument'. 

Answer 9

I have dragged and dropped PERNR into the values field and my result was a count on the PERNRs.  The results are still not what I am looking for.

Answer 10

This is my result pivot table looks like (The relationship I have is Exec[PERNR] to Email[PERNR]) -

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 11

Can you describe what layout that you are looking for? Best with some illustration:)

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 12

This formula will work in the PowerPivot screen.  It might work in the Pivot Table view, but the syntax would be more complex.  I would recommend you add it to PowerPivot because it will be usable across multiple Pivot Tables and will be easier to work with on the sheets.

---

Regards, Steve @dataonwheels http://www.dataonwheels.com

Answer 13

You have the layout that I am looking for. 

I am not getting any value for the email address now.  My results are as follows:

Answer 14

I will try to add the formula in the Pivot Table View.  Thank you very much.

Answer 15

Is there any particular reason why I don't see any email addresses for the specified employees form the illustration that I sent on April 9th?  What am I doing incorrectly?  Please advise.

Answer 16

When I added the formula to the Pivot Table view I didn't get any results.  I used the formula: =RELATED(EmailAddress[Email]) as an added column on my Employee Name(Exec) table.

Any suggestions?

Answer 17

I can't think of why - can you email your workbook to chuxu @ hotmail.com so I can take a look?

Chu

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 18

I will send from my hotmail account.

Answer 19

Figured out your problem: You have PERNR defined as "Decimal Number" data type in EmployeeName table and "Text" in EmailAddress table. Once I change them both to WholeNumber data type, your email address then show up fine.

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 20

THANK YOU SO MUCH!  YOU GET ALL THE KUDOS!