Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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 )
)
Proud to be a Super User!
@DataInsights , that works perfectly! I didn't think to use GENERATE. This had me stumped, so huge kudos for helping with this.
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 )
)
Proud to be a Super User!
User | Count |
---|---|
47 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |