Dynamic filter with Related Table Condition

[Thales_Taranto]

Hello everyone!! Hope u are all good.

I developed an app for a friend, and i created a Dynamic Filter Dashboard based on some links here. But now he asked me to add another condition that would filter a related Table and i need help on two things:

This app has a Order Table, a Product Table and a OrderProduct Table (to deal with the many-to-many relationship). My filter slice setting is at Order's Table, that has a Related OrderProduct (App Formula: REF_ROWS("OrderProduct" , "Order ID")).

•  Changing the row condition formula to include this Related Product filter:

  The way i tried to manage it was to use the following row filter condition inside the AND([ALL FILTER FORMULAS]

  IF(
  ISBLANK(DashboardFilter[Product ID]),
  true,
  IN([Related OrderProduct][Product ID],DashboardFilter[Product ID])
  )

      The problem is that this IN() formula is not working for multiple values. Since the [Product ID] at DashboardFilter is a Enumlist ref I need it to work based on multiple values and not single one. 

• Show only the related Products matching the filter condition:

Since the slice is based on Order Table the same is for the card view.  And at the "image" card setting i set to show [Related OrderProduct] as the image bellow. So it will show the order that matches the filter condition but will not restrict the products view at the [Related OrderProduct] card view. Is it possible to in addition to show the orders based on the filter, showing the products too?

[VOLODYMYR_TYROL]

Hi, good question.

I'm not sure that my solution is right, but I use CONTAINS() to find something in Enumlist, because Enumlist it's a text separated by a comma.

https://support.google.com/appsheet/answer/10107955?hl=en 

[Thales_Taranto]

Thank for your suggestion but it didn't worked also. It is returning only the columns that matches all the selected itens. For exemple:

Order 001 - ITEM A, ITEM B, ITEM C

Order 002 - ITEM A, ITEM B

Order 003 - ITEM C

If my enumlist has the ITEM A and the ITEM B selected it should return the orders 001 and 002. 

With your formula it will return only the order 002. I think the way CONTAINS work is different from what you said, the list of itens will be a single text like "ITEM A, ITEM B" and will result true whenever the column is filled exactly with this text. 

[VOLODYMYR_TYROL]

CONTAINS("abc", "bc") returns TRUE

[Thales_Taranto]

Yeah it does, but CONTAINS("b", "bc") return FALSE, and in my case it should return TRUE.

What this case means, supose the user is searching for item b or item c on the filter, and this order only has the item b. As item b is on the criteria, this row should appear on the filter and it isn't. I don't know if i made it clear enough.

[Steve · Accepted solution]

OR(
  ISBLANK(DashboardFilter[Product ID]),
  ISNOTBLANK(
    INTERSECT(
      DashboardFilter[Product ID]),
      [Related OrderProduct][Product ID]
    )
  )
)

[Thales_Taranto]

Thanks Steve. You're always saving us!!!! By the way, i managed to get this product list inside of the Product table, since that is a intermediate Table that relates Products and Orders. 

I just did the [Related IntermediateTable][Column I wanted] in Product table, and i got the result almost the way my friend expected. I'll give a try to your suggestion.