none
Import CrossJoin table

    Question

  • Hello Gurus

    My PowerPivot tables take data only from the same Excel file, no external connections. Unfortunately, all tables that I import into PowerPivot are same as in Excel sheets. But I need a table that does not exist in Excel but can be derived. I need a cross-join table.

    E.g., I have a table of monthly sales, table of products, table of shareholders, table of share distribution per project/product/shareholder. Now I need a cross-join table with key fields Shareholder/Product/Month in order to have granular data for further pivot analysis of earnings per shareholder or per product for any period of time. Without such type of table all calculations are not granular enough to have proper slices.

    In other words, how do I make a cross join from tables located in the same file without sophisticated SQL servers and queries.

    Thanks!

    Sunday, June 08, 2014 5:09 PM

Answers

  • Hi Michael,

    The Linkback Table feature may work for your scenario. In my test I have created two tables in Excel and added them to the Power Pivot model.

    From a blank worksheet, I went to the 'Data' tab > 'Existing Connections' > 'Tables' tab > Selected 'Table1' under the 'This Workbook' section > Clicked 'Open' > Made sure the 'Table' radio button was selected on the 'Import Data' form then clicked 'OK' > Right clicked on the new table > Picked 'Table' > 'Edit DAX...' from the context menu > Changed the 'Command Type' to 'DAX' on the 'Edit DAX' form > Entered my CROSSJOIN DAX query > Clicked 'OK'.

    Hopefully the following screenshots illustrate these steps and outcome...

    Your final step would be to add back this new cross joined table to the Power Pivot model using the 'Add to Data Model' option under the Power Pivot tab.

    EDIT:

    The CROSSJOIN DAX query that was used in my test was:

    EVALUATE
    CROSSJOIN(
      Table1,
      Table2
    )


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn


    Thursday, July 10, 2014 1:46 PM

All replies

  • Try to add the same table (sheet) under another name, and use your CROSS JOIN
    Sunday, June 08, 2014 11:51 PM
  • Hi Mahfoud,

    Thanks! But I guess creation of a new sheet is the same effort as creation of a new sheet with already cross joined tables.

    By the way, how to add a table and use cross join? I thought Cross Join is a formula and not a way to link a table?

    Thanks!

    Monday, June 09, 2014 11:04 AM
  • Hi Michael,

    If you're able to install it on your machine, Power Query may allow you to achieve what you're after. Perhaps this similar thread will help: http://social.technet.microsoft.com/Forums/en-US/849833aa-7767-4a7f-8ec0-18fb342dc84d/cross-join-in-power-query?forum=powerquery


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn

    Wednesday, June 11, 2014 2:39 PM
  • Hi Michael,

    Thanks for an advice. Unfortunately, this will not help. In this thread the person asks about CrossJoin but the others recommend him the Full Outer Join which are different things.

    Wednesday, June 11, 2014 7:43 PM
  • Hi Michael,

    I'm in full agreement with you that a full outer join and cross join aren't the same :) I should have been more specific; I was actually referring you to Chris Webb's posts within the thread.


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn

    Wednesday, June 11, 2014 7:53 PM
  • Hi Michael,

    Thanks! Chris Webb has a lot of interesting articles in his blog. There are easy ways to crossjoin using SQL statements. However, these statements are applicable for external SQL DB's. In y case all data is within my file so I don't see any reason to create a SQL DB somewhere on the internet. I'm able to split my project into 2 files, and call one from another. In this case it's an emulation of an external DB. However, in this case the path to the file is not usable on the internet, e.g. path to file becomes C:\My Documents..., but neither PowerPivot nor PowerQuery accept URL's.

    Wednesday, June 11, 2014 9:52 PM
  • Michael,

    Is this still an issue?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, June 27, 2014 11:35 PM
  • Hi Ed,

    Yes, it's still an issue.

    BR,

    Michael

    Sunday, July 06, 2014 5:00 PM
  • Hi Michael,

    The Linkback Table feature may work for your scenario. In my test I have created two tables in Excel and added them to the Power Pivot model.

    From a blank worksheet, I went to the 'Data' tab > 'Existing Connections' > 'Tables' tab > Selected 'Table1' under the 'This Workbook' section > Clicked 'Open' > Made sure the 'Table' radio button was selected on the 'Import Data' form then clicked 'OK' > Right clicked on the new table > Picked 'Table' > 'Edit DAX...' from the context menu > Changed the 'Command Type' to 'DAX' on the 'Edit DAX' form > Entered my CROSSJOIN DAX query > Clicked 'OK'.

    Hopefully the following screenshots illustrate these steps and outcome...

    Your final step would be to add back this new cross joined table to the Power Pivot model using the 'Add to Data Model' option under the Power Pivot tab.

    EDIT:

    The CROSSJOIN DAX query that was used in my test was:

    EVALUATE
    CROSSJOIN(
      Table1,
      Table2
    )


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn


    Thursday, July 10, 2014 1:46 PM
  • Dear Michael,

    This is exactly what I needed. With your help I've discovered this LinkBack functionality. Now it's all sorted out. Thank you very much!

    BR, Michael

    Thursday, July 10, 2014 6:23 PM
  • Dear Michael,

    One more quick question related to this topic if you don't mind. The general crossjoin formula creates crossjoin from all columns. Is there a way to limit the columns. E.g. in your example I'd like to have only column Test1 from 1st table and Test3 from second table.

    Thanks!

    BR, Michael

    Friday, July 11, 2014 7:26 PM
  • You could try something like this...

    EVALUATE
    CROSSJOIN(
      VALUES(Table1[Test1]),
      VALUES(Table2[Test3])
    )

    Edit:

    The above approach will work when you want to cross join a single column from each table. If you want to pick multiple columns that should be cross joined then you could use the SUMMARIZE function instead of VALUES like this...

    EVALUATE
    CROSSJOIN(
      SUMMARIZE(
        Table1,
        Table1[Test1],
        Table1[Test2]
      ),
      SUMMARIZE(
        Table2,
        Table2[Test3],
        Table2[Test5]
      )
    )

    The SUMMARIZE function can be a bit expensive performance wise but this will depend on your data.

    VALUES function: http://msdn.microsoft.com/en-us/library/ee634547.aspx

    SUMMARIZE function: http://msdn.microsoft.com/en-us/library/gg492171.aspx


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn



    Saturday, July 12, 2014 12:09 AM
  • Dear Michael,

    Thanks!! It's working as expected now!

    BR, Michael

    Sunday, July 13, 2014 1:16 PM
  • No problem. Glad it help :)

    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn

    Sunday, July 13, 2014 5:24 PM