Forum Discussion

vpatel55's avatar
vpatel55
Icon for Kudo Commander rankKudo Commander
5 years ago
Solved

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 combinat...
  • DataInsights's avatar
    DataInsights
    5 years ago

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

     

     

DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
5 years ago

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