cancel
Showing results for
Did you mean:
Kudo Collector

Creating a new table with a cartesian product column

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.

1 ACCEPTED SOLUTION
Super User

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

2 REPLIES 2
Kudo Collector

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

Super User

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

Announcements

Launching new user group features

Learn how to create your own user groups today!