Show all items in an order when one product is filtered

[ciken]

I'm trying to create a table that shows all items in an order when a specific coupon is used. I've created the visual table with all orders and the OrderedProductName field. When I add a filter on that field to only show a specific coupon used, then I lose all the other line items in that order. See below for example. 

Not sure if this should be a measured field, a new data table to filter from, concatenatex or some other variation to allow all ordered products within an order number to show when a filter is selected...

Thanks

FULL DATA TABLE

Order Number                OrderedProductname              

1                                      Bananas

1                                      Coupon: 5off

2                                      Apples

3                                      Oranges

3                                      Coupon: neworder

FILTER VISUAL: OrderedProductName = Coupon: neworder

Table should now show: 

Order Number:             OrderedProductName

3                                    Oranges

3                                    Coupon: neworder

[v-rzhou-msft]

Hi @ciken 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table or share me with your pbix file from your onedrive business.

Best Regards,

Rico Zhou

[v-rzhou-msft]

Hi @ciken 

Just like mahoneypat  and lbendlin replied before, you can build a Slicer and use a Measure to achieve your goal.

Firstly, create a new table in Modeling Tab:

Table 2 = VALUES('Table'[OrderedProductname])

Then we can build a Slicer by this new table.

Then we create a measure:

Click New measure in Home Tab

Measure =

var _a = SELECTEDVALUE('Table 2'[OrderedProductname])

var _b = CALCULATE(MAX('Table'[Order Number]),'Table'[OrderedProductname]=_a)

return

CALCULATE(MAX('Table'[OrderedProductname]),'Table'[Order Number]=_b)

Then we add the Measure into our table visual, and select OrderedProductname in Slicer.

Result:

You can download the pbix file form this link:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/tongzhou_qiuyunus_onmicrosoft_com/EUe2rXC0JsNKlQpv...

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]

While @mahoneypat 's proposal covers the text of your request I noticed that the title of your request implies a different ask.  A more generic solution would be to have a shadow table that would feed your slicer, and a measure in your original table that looks at the slicer selection and that you then use to suppress the display of rows based on the selection in the shadow table. That way any item in an order can be selected to show all items in that order.

[mahoneypat · Accepted solution]

The simplest way to do this would be to add a calculated column that shows the coupon code for every row of the that order, with an expression like below.  You can use this new column in your slicer.

Coupon =
CALCULATE (
    MIN ( Coupons[OrderedProductName] ),
    ALLEXCEPT ( Coupons, Coupons[Order Number] ),
    CONTAINSSTRING ( Coupons[OrderedProductName], "Coupon" )
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[ciken]

Thank you, this worked perfectly!