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

  • 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                      joeblack@work.com                                      1111                       Ed Green

     1002                      donred@work.com                                        1222                       Mike Smith

     1111                      edgreen@work.com                                      1333                       Eric Small

     1222                      mikesmith@work.com 

     1333                      ericsmall@work.com

     1334                       tinafey@work.com

     1336                       roypoindexter@work.com

     1385                       garygrey@work.com     

    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 ?

    Thursday, March 29, 2012 9:10 PM

Answers

  • 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

    • Marked as answer by Chris McElrath Wednesday, April 25, 2012 4:01 PM
    Wednesday, April 18, 2012 5:53 AM

All replies

  • 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

    Thursday, March 29, 2012 10:18 PM
  • Thank you very much.   I had not thought about using a measure to correct my expected results.  Hopefully this will resolve my issue.

    Friday, March 30, 2012 5:08 PM
  • 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?

    Friday, March 30, 2012 9:51 PM
  • My data into Power Pivot is as follows:

    A.

           Emails                                                                                     ExecBu

    PERNR                      Email                                                                PERNR                  Name

     1001                      joeblack@work.com                                      1111                       Ed Green

     1002                      donred@work.com                                        1222                       Mike Smith

     1111                      edgreen@work.com                                      1333                       Eric Small

     1222                      mikesmith@work.com 

     1333                      ericsmall@work.com

     1334                       tinafey@work.com

     1336                       roypoindexter@work.com

     1385                       garygrey@work.com     

    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?

    • Merged by Challen Fu Monday, April 2, 2012 7:07 AM duplicated thread
    Friday, March 30, 2012 10:51 PM
  • 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

    Sunday, April 1, 2012 6:19 AM
  • 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

    • Proposed as answer by Challen Fu Monday, April 2, 2012 7:08 AM
    Monday, April 2, 2012 4:19 AM
  • 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'. 

    Thursday, April 5, 2012 10:57 PM
  • 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'. 

    Thursday, April 5, 2012 11:00 PM
  • 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.

    Thursday, April 5, 2012 11:45 PM
  • 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

    Friday, April 6, 2012 12:09 AM
  • 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

    Friday, April 6, 2012 6:10 AM
  • 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

    Monday, April 9, 2012 4:25 PM
  • 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:

    Monday, April 9, 2012 10:55 PM
  • I will try to add the formula in the Pivot Table View.  Thank you very much.
    Monday, April 9, 2012 11:02 PM
  • 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.
    Wednesday, April 11, 2012 4:32 PM
  • 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?

    Wednesday, April 11, 2012 5:16 PM
  • 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

    Thursday, April 12, 2012 11:16 PM
  • I will send from my hotmail account.
    Friday, April 13, 2012 8:28 PM
  • 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

    • Marked as answer by Chris McElrath Wednesday, April 25, 2012 4:01 PM
    Wednesday, April 18, 2012 5:53 AM
  • THANK YOU SO MUCH!  YOU GET ALL THE KUDOS!

    Wednesday, April 25, 2012 4:03 PM