Creating a new table with a cartesian product column

[vpatel55]

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.

[vpatel55]

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

[DataInsights · Accepted solution]

@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 )
)