Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Filter a table visual if it contains values from a filter which is based on an unrelated table

Hello friends,

I have 2 tables

Tables1

CustomerProduct
AOranges, Apple, Banana, Melon
BApple
C

Banana

D

Peach, Banana

E

Peach, Melon

 

Tables2

Product_Type
Apple
Peach
Banana
Melon
Orange


I want to create a filter Based on "Table2" , I then want Table1 visual to change based on the filter choices of Table2

ie if the user select "Apple" and "Melon" in the filter then Table2 visual will show Customers A,B and E

hope this explains things

thankyou

Frank

General Comment
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    HI Anonymous,

    You can try to use the following measure formula to compare between two table field values and return flag, then you can use it on visual level filter to filter records:

    formula =
VAR productList =
    CALCULATE (
        CONCATENATEX ( VALUES ( Table1[Product] ), [Product], "," ),
        ALLSELECTED ( Table1 ),
        VALUES ( Table1[Customer] )
    )
VAR result =
    COUNTROWS (
        FILTER (
            ALLSELECTED ( Table2 ),
            SEARCH ( Table2[Product_Type], productList, 1, -1 ) > 0
        )
    )
RETURN
    IF ( result > 0, "Y", "N" )

    Regards,

    Xiaoxin Sheng

6 Replies

  • vicky_'s avatar
    vicky_
    Icon for Super User rankSuper User
    3 years ago

    You can use this guide on setting up an XOR filter based on your selections: https://apexinsights.net/blog/or-xor-slicing

    and then you can use CONTAINSSTRING() as part of your filter conditions.

     

    Honestly, I would recommend that you go back to PowerQuery / your data source and change the data structure so that you don't have to use CONTAINSSTRING(), and can just match the entire cell contents.

  • AlanP514's avatar
    AlanP514
    Icon for Post Patron rankPost Patron
    3 years ago

    Hai Anonymous 

    Testing =

    VAR SelectedProduct = SELECTEDVALUE(Tables2[Product_Type])
    RETURN
    CONCATENATEX(
    FILTER(
    Tables1,
    CONTAINSSTRING(Tables1[Product], SelectedProduct)
    ),
    Tables1[Customer],
    ", "
    )
    try this code

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello Alan P514,

      thankyou very much for your code above, it works but the only issue I have is that when I place the data in a a table visual all of the data is in one row instead of one row per customer name

      I appologies as It was my fault, as I should have said 
      output = 

      Customer
      A
      B
      E