Forum Discussion

Merleau's avatar
Merleau
Helper 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    Name Code Id1 Id1_name         A Id2 Id2_Name      B Id3 Id3_Name...
Need Help
Show and Tell
Tips and Tricks
Sahir_Maharaj's avatar
Sahir_Maharaj
Super 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
Helper 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.