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




    1000 123 bob smith 1000123

    1001 84 jane marly 100184

    1002 4053 mike scott 1024053





    Table 2

    RegID UserID RegistrationDate Regkey




    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


All replies