Breaking down count measure in table visual

[peter_mx5]

Hello,

I have the following table "order_details_ss" (filtered for product_id 14):

Assuming a slicer filters for product id = 162. My goal is to create a measure that counts how often a product has been ordered together with product 162 (meaning appearances in the same order_id). Putting this measure in a table visual should result into this:

product_id	times bought together
2452	1
8575	1
10096	1
20392	1
20995	1
23032	1
27845	1
39475	1
41890	1
45066	1

Product 162 appears only in order_id 14, so thats why the count is 1 for each product in this example.

The measure I created counts the number of products that are purchased together with product 162 (10 in this example):

Customers also bought test 3 = 
VAR SelectedProduct = SELECTEDVALUE('order_details_ss'[product_id])

-- Get the orders in which the Selected Product was bought
VAR OrdersWithSelectedProduct = 
    CALCULATETABLE(
        VALUES('order_details_ss'[order_id]),
        'order_details_ss'[product_id] = SelectedProduct
    )

-- Get the list of products bought together with the Selected Product
VAR ProductsBoughtTogether =
    CALCULATE(
    COUNT('order_details_ss'[product_id]),
    FILTER(
        ALL('order_details_ss'),
        'order_details_ss'[order_id] IN OrdersWithSelectedProduct &&
        'order_details_ss'[product_id] <> SelectedProduct
    )
)

-- Count how many times each product was bought together with the Selected Product
RETURN
    ProductsBoughtTogether

But I fail braking down this measure like I showed in the table above when placing this measure together with the product_id in a table-visual:

How should I fix my measure? Thanks in advance

[Anonymous · Accepted solution]

Hi @peter_mx5 ,

Thanks for the reply from @lbendlin .

Please try:

Create a disconnected table:

product_id
158
159
162
200
2345
1234
123
1
88

Drag the product id of the new table to the slicer:

Create a measure:

times bought together =
VAR_curProduct=
     SELECTEDVALUE ( 'Pid'[product_id] )
VAR_orderid=
     CALCULATE (
         MAX ( 'order_details_ss'[order_id] ),
         'order_details_ss'[product_id] = _curProduct
     )
RETURN
     IF (
         MAX ( 'order_details_ss'[order_id] ) = _orderid
             && MAX ( 'order_details_ss'[product_id] ) <> _curProduct,
         CALCULATE (
             DISTINCTCOUNT ( order_details_ss[product_id] ),
             ALLSELECTED ( 'Pid'[product_id] )
         ),
         BLANK()
     )

Set the filter condition in the Filter pane to measure not blank.

The table on the left is the original table. When 162 is selected, the table on the right displays data with the same order_id as 162.

pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[lbendlin]

Read about REMOVEFILTERS.  Make sure to subtract 1 from the result to exclude the starting product ID (162 in your case)