Creating a new table with a cartesian product column

Question

Hello,&nbsp;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.&nbsp;The input is this:&nbsp;Transaction IdCustomerProduct1John SmithGrapefruit1John SmithOrange1John SmithPineapple2Lucy JonesMelon2Lucy JonesJackfruit3Amy SycamoreGrapefruit4Daniel JohnsonOrange5Jimmy JohnsonPineapple&nbsp;The expected output is this, where Product 1 and Product 2 show every combination of product in that Transaction id.&nbsp;Transaction IdCustomerProduct 1Product 21John SmithGrapefruitGrapefruit1John SmithGrapefruitOrange1John SmithGrapefruitPineapple1John SmithOrangeGrapefruit1John SmithOrangeOrange1John SmithOrangePineapple1John SmithPineappleGrapefruit1John SmithPineappleOrange1John SmithPineapplePineapple2Lucy JonesMelonMelon2Lucy JonesJackfruitMelon2Lucy JonesMelonJackfruit2Lucy JonesJackfruitJackfruit3Amy SycamoreGrapefruitGrapefruit4Daniel JohnsonOrangeOrange5Jimmy JohnsonPineapplePineapple&nbsp;A link to the input file is here:&nbsp;https://www.dropbox.com/s/m4uffww780eoi71/Sample%20file.pbix?dl=0&nbsp;A kudos for anyone with a solution. Thanks in advance.

datainsights · Accepted Answer

vpatel55,
&nbsp;
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.
&nbsp;
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 )
)
&nbsp;

&nbsp;

Forum Discussion

Creating a new table with a cartesian product column

2 Replies

Related content

Data model relationship creates cartesian product

Relationships error - Cartesian product

DAX Measure causes Cartesian product in table

DAx Support on cartesian table

Create new table applying cartesian logic

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

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