1 2 Previous Next 22 Replies Latest reply on Jan 31, 2018 9:51 AM by tableau gstl

    CROSS JOIN with Tableau's join dialog


      August 15, 2017


      This article was originally posted March 28, 2016. Version 9.0 of Tableau was used.


      Dynamic Cross Join - Join (Insurance).pngFrom release 10.2 it is possible to use dummy values 1 = 1 in Tableau's join dialog and thus not necessary to create dummy columns in data itself.


      The screenshot on the right side is copied from  Split Periods Into Rows Dynamically  which basically is about the same except it uses a more flexible lookup table strategy.






      March 28, 2016


      Did you know that we can cross join tables with Tableau's join dialog?

      In case you didn't:

      This is done by adding dummy keys to both tables with one and the same value in all rows (such as 1=1).


      This gives us an all-to-all relationship of the equi-join – a new term just coined for easier understanding and comparison with similar terms such as a  many-to-many relationship and alike.


      Have you used Tableau's join dialog for cross joining two tables?

      If yes, what is your experience?

      If no, but like to, please share your findings.


      Example of a cross product of 140,000,000 rows ( data 700,000 x lookup 200 )

      The gif below shows how surprisingly well Tableau responds with a data table of 700,000 rows and lookup table of 200 rows!





      1. As shown above: It is fast, very fast!

      2. No inflation of rows to store! The cross join is done on the fly by Tableau.




      The guide below shows how. See also attached xlsx and twb file.



      Warning: Don't Extract!

      You can save the file as twbx without any issues (still fast, still no inflation of rows).

      But if you thereafter extract the twbx connection, you will get a full cross product!



      Step 1: Add dummy keys to both tables

      Add a dummy key containing one and the same value in both tables, such as CrossJoinKey seen below:



      The attached Excel file contains 50,000 rows, but can easily be expanded to more by copying the rows.

      The values will be random, because the columns [PeriodStart], [PeriodEnd] and [Amount] are populated with RANDBETWEEN formulas.



      Step 2: Connect to data and join tables







      Step 3: Filter







      Step 4: Build view







      Step 5: Test performance with Quick Filter







      Step 6: Test performance with calculation [Days in Period]







      Step 7: Test performance with more demanding calculation [Amount in Period]






      Attached Tableau Version: 9.0


        1 2 Previous Next