topic Re: Creating a new table with a cartesian product column in DAX Commands and Tips

Creating a new table with a cartesian product column

vpatel55 — Wed, 09 Jun 2021 16:53:12 GMT

Hello,

I am trying to create a new table containing every combination of Product in within one transaction id. So if transaction 1 contained 3 different products, that would be 3 x 3 = 9 combinations. This needs to be done in DAX.

The input is this:

Transaction Id	Customer	Product
1	John Smith	Grapefruit
1	John Smith	Orange
1	John Smith	Pineapple
2	Lucy Jones	Melon
2	Lucy Jones	Jackfruit
3	Amy Sycamore	Grapefruit
4	Daniel Johnson	Orange
5	Jimmy Johnson	Pineapple

The expected output is this, where Product 1 and Product 2 show every combination of product in that Transaction id.

Transaction Id	Customer	Product 1	Product 2
1	John Smith	Grapefruit	Grapefruit
1	John Smith	Grapefruit	Orange
1	John Smith	Grapefruit	Pineapple
1	John Smith	Orange	Grapefruit
1	John Smith	Orange	Orange
1	John Smith	Orange	Pineapple
1	John Smith	Pineapple	Grapefruit
1	John Smith	Pineapple	Orange
1	John Smith	Pineapple	Pineapple
2	Lucy Jones	Melon	Melon
2	Lucy Jones	Jackfruit	Melon
2	Lucy Jones	Melon	Jackfruit
2	Lucy Jones	Jackfruit	Jackfruit
3	Amy Sycamore	Grapefruit	Grapefruit
4	Daniel Johnson	Orange	Orange
5	Jimmy Johnson	Pineapple	Pineapple

A link to the input file is here:

https://www.dropbox.com/s/m4uffww780eoi71/Sample%20file.pbix?dl=0

A kudos for anyone with a solution. Thanks in advance.

Re: Creating a new table with a cartesian product column

DataInsights — Wed, 09 Jun 2021 22:53:51 GMT

vpatel55,

Try this calculated table. The SELECTCOLUMNS function is necessary in order to rename the Product column, since CROSSJOIN doesn't allow two columns with the same name.

Cross Join = GENERATE ( SUMMARIZE ( Data, Data[Transaction Id], Data[Customer] ), VAR vTransId = Data[Transaction Id] VAR vProduct1 = CALCULATETABLE ( VALUES ( Data[Product] ), Data[Transaction Id] = vTransId ) VAR vProduct2 = SELECTCOLUMNS ( CALCULATETABLE ( VALUES ( Data[Product] ), Data[Transaction Id] = vTransId ), "Product2", Data[Product] ) RETURN CROSSJOIN ( vProduct1, vProduct2 ) )

Re: Creating a new table with a cartesian product column

vpatel55 — Thu, 10 Jun 2021 04:51:27 GMT

DataInsights , that works perfectly! I didn't think to use GENERATE. This had me stumped, so huge kudos for helping with this.