locked
Combine data from multiple Measures results in a Crossjoin in pivot table RRS feed

  • Question

  • I have two tables within my PowerPivot sheet. when I try to merge the contents within an excel pivot table, I only get cross join results.

    tables look like this:

     

    RegID userID Name Regkey

    (calculated)
     

     

    ----------------------------------------------------------------------
     

    1000 123 bob smith 1000123

    1001 84 jane marly 100184

    1002 4053 mike scott 1024053

     

     

     

     

    Table 2

    RegID UserID RegistrationDate Regkey

    (calculated)
     

     

    -----------------------------------------------------------------------
     

    1000 123

    '5/12/2011' 1000123

    1001 84

    '5/14/2011' 100184

    1002 4053

    '1/12/2011' 1024053

     

    I added the RegKey column as a calculated column after importing the tables into PowerPivot using the concat function. I set up the table relationship to join on the regkey since it is a 1:1 relationship between the two tables. I expect to be able in the excel pivot table to grab the regid, userid, and name from table1, and the registration date from table2 and have a distinct result set. instead, for every row in table one I get every row in table2.

    Can someone please tell me how I can get just the intersecting rows instead?

    thanks in advance!!

    Sunday, June 5, 2011 7:59 PM

Answers

All replies