Forum Discussion

peter_mx5's avatar
peter_mx5
New Member
2 years ago
Solved

Breaking down count measure in table visual

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_idtimes bought together
24521
85751
100961
203921
209951
230321
278451
394751
418901
450661

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's avatar
    Anonymous
    2 years ago

    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!

2 Replies

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

  • Anonymous's avatar
    Anonymous
    Not applicable

    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!