Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX generated table with related data

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   ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    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.

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

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)
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.