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 IDTransaction IDQty
AX1
BX2
CX1
AY2
BY1
AZ1
CZ1

 

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 IDQtyCount of Transactions
AB32
AC22
BA32
BC11
CA22
CB21

 

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

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.

4 Replies

  • lbendlin's avatar
    lbendlin
    Super 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks, this works perfectly