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

[Merleau]

Hello

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

Table 1

Id	Name	Code
Id1	Id1_name	A
Id2	Id2_Name	B
Id3	Id3_Name	AL
Id4	Id4_Name	BC
Id5	Id5_Name	L

Table2

key	Region	Code list	Name
t1	reg1	L,	Name1
t2	reg2	A,L,	Name2
t3	reg3	B,A,	Name3
t4	reg4	BL,AL,A,	Name4
t5	reg5	BC,	Name5
t6	reg6	A,	Name6
t7	reg7	A,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.

[v-xiaocliu-msft]

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]

Hello @v-xiaocliu-msft 

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,

[Sahir_Maharaj]

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]

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.