DAX generated table with related data

[lmbldn]

Hello all,

I have a powerbi report containing a table that looks like :

Product ID	Transaction ID	Qty
A	X	1
B	X	2
C	X	1
A	Y	2
B	Y	1
A	Z	1
C	Z	1

I would like to create a new table in dax, that would show for each unique occurence of the prodcut ID in the previous table, each product that has been bought at the same time (=in a transaction), and the sum of qty associated.

So for the example above, that would give a table like :

Product ID	Associated Product ID	Qty	Count of Transactions
A	B	3	2
A	C	2	2
B	A	3	2
B	C	1	1
C	A	2	2
C	B	2	1

I hope this is clear enough.

In SQL I could probably pull it off, but I have no idea how to this in DAX (using a DAX generated table)

Can somebody help me out ?

Thanks

[v-rzhou-msft · Accepted solution]

Hi @lmbldn 

You need to duplicate a same table like original one, and change all the column names in new table. Because Power BI doesn't support two columns have the same name. Then you can build a dax code as below to build the table you want.

New Table =
VAR _T =
    CROSSJOIN ( 'Table', 'Ass Table' )
VAR _T2 =
    SUMMARIZE (
        FILTER (
            _T,
            [Product ID] <> [Ass Product ID]
                && [Transaction ID] = [Ass Transaction ID]
        ),
        [Product ID],
        [Ass Product ID],
        "QTY", SUM ( 'Table'[Qty] ),
        "Count of Transactions", COUNT ( 'Table'[Transaction ID] )
    )
RETURN
    _T2

Result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lmbldn]

Thanks, this works perfectly

[lbendlin]

I think your sample result has a typo.

Here's the general approach.

- take your original table

- duplicate it and rename all columns

- create a new table as a cross join of the two tables

- eliminate all rows where the products are the same, and all rows where the transaction IDs are not the same.

- load into the table visual

Alltrans = CROSSJOIN(Transactions,Transactions2)

Show = switch(TRUE(),Alltrans[Product ID]=Alltrans[Ass Product ID],0,Alltrans[Transaction ID]<>Alltrans[Ass Transaction ID],0,1)

[lmbldn]

Thanks for your help. I went with the SUMMARIZE() approach as I than could have the count of transactions in the table, but thank you for breaking the logic down for me, much appreciated.