cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lmbldn
Regular Visitor

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

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

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.

1.png

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.

View solution in original post

4 REPLIES 4
RicoZhou
Community Support
Community Support

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.

1.png

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.

View solution in original post

Thanks, this works perfectly

lbendlin
Super User
Super User

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

 

lbendlin_0-1624228156005.png

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

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!