Forum Discussion

Merleau's avatar
Merleau
Icon for Helper II rankHelper II
1 year ago

Cross filtering: How to cross filter a list column using a value from a different table

Hello

 

In my report I need to cross filter two tables which are not linked in my data model.

 

Table 1

Id   NameCode
Id1Id1_name        A
Id2Id2_Name     B
Id3Id3_Name  AL
Id4Id4_Name  BC
Id5Id5_Name  L

 

 

Table2

key

RegionCode listName
t1       reg1       L,      Name1
t2reg2A,L,Name2
t3reg3B,A,Name3
t4reg4BL,AL,A,      Name4
t5reg5BC,Name5
t6reg6A,Name6
t7reg7A,B,BL,Name7

 

 

What I need, is the following.

If a user click on any row in Table1, then the rows in Table 2 that contain the code  selected in Table 1 are all highlighted or filter out.

 

e.g

In Table1, I select row 2 with code = B, then in Table2, rows 3 and 7 are all selected.

 

Again these two tables are not linked in my model.

Any help is greatly appreciated.

Thank you.

 

 

Need Help
Show and Tell
Tips and Tricks

4 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    1 year ago

    Hello Merleau,

     

    Can you please try creating a Bridge Table - Then create relationships and enable Cross-Filtering.

    BridgeTable = 
DISTINCT(
    UNION(
        SELECTCOLUMNS('Table1', "Code", 'Table1'[Code]),
        SELECTCOLUMNS('Table2', "Code", PATHITEM('Table2'[Code list], 1, TEXT)),
        SELECTCOLUMNS('Table2', "Code", PATHITEM('Table2'[Code list], 2, TEXT)),
        SELECTCOLUMNS('Table2', "Code", PATHITEM('Table2'[Code list], 3, TEXT))
    )
)
    • Merleau's avatar
      Merleau
      Icon for Helper II rankHelper II
      1 year ago

      Hello Sahir_Maharaj 

      Thank you so much for your response.

      Could pls provide further details?

      I am not able to create that bridge table from your response.

      Many thanks, again.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Merleau ,

     

    Please try this

    Measure = IF( SEARCH(TRIM( SELECTEDVALUE(Table1[Code])), SELECTEDVALUE('Table2'[Code list]), 1, 0)<>0, SELECTEDVALUE('Table2'[Code list]))

     

    Best Regards,

    Wearsky

    • Merleau's avatar
      Merleau
      Icon for Helper II rankHelper II
      1 year ago

      Hello Anonymous 

      Thank you for your prompt response. The measure works but only consistently for codes with double letters. For single-letter codes, it sometimes returns records that include double-letter codes where one of the letters matches the single-letter code.

      For example, selecting "L" (Line 5 in Table1) also returns "BL, AL, A" (Line 4 in Table2).

      Could you advise on how this can be fixed?

       

      My real data have a long list of codes most are single-letter and an even longer list of "Code list" that include different codes combination.

       

      Thank you,