Filtering two tables with a slicer by a couple of shared columns

[trevordunham]

I have two tables that contain a few shared columns that I want to use as slicers to filter both sets of data at once. I do not wish to combine these tables as they are pretty different data and the are also separate API connections.

For the first shared column I tried to build a new table to use as a filter instead as so:

Shared Table=

DISTINCT(UNION(

    DISTINCT('Table1'[Shared Column]), DISTINCT('Table2'[Shared Column])))

When I try to create a relationship between this table and either table 1 or 2, I am not able to select one to many due to cardinality issues. 

I also tried:

Shared Table = UNION(SELECTCOLUMNS('Table 1', "Shared Column", 'Table1'[Shared Column]), SELECTCOLUMNS('Table2', "Shared Column", 'Table2'[Shared Column]))

I get the same issue with creating a relationship. The only option is many to many which does not work when I use this cardinality and try to use my shared table column as a filter.

What am I missing? Is there a workaround or different method to do this?

[Kishore_KVN · Accepted solution]

Hello @trevordunham ,

You may need to create new table with unique values and use them in slicer to filter data (this may work as this is one of the way for this kind of similar situations)

Table can be created using below DAX:

SharedTable = UNION(DISTINCT('Table1'[Shared Column]), DISTINCT('Table2'[Shared Column]))

Then create a filter expression to filter data in the visuals using below DAX:

FilteredData = 
FILTER(
    'Table1',
    'Table1'[Shared Column] IN VALUES(SharedTable[Shared Column])
)

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[trevordunham · Accepted solution]

I discovered an error in my original table, using my first table: 

Shared Table=

DISTINCT(UNION(

    DISTINCT('Table1'[Shared Column]), DISTINCT('Table2'[Shared Column])))

then just using many to many relationships on table 1 and 2 does work.

[Kishore_KVN · Accepted solution]

Hello @trevordunham ,

You may need to create new table with unique values and use them in slicer to filter data (this may work as this is one of the way for this kind of similar situations)

Table can be created using below DAX:

SharedTable = UNION(DISTINCT('Table1'[Shared Column]), DISTINCT('Table2'[Shared Column]))

Then create a filter expression to filter data in the visuals using below DAX:

FilteredData = 
FILTER(
    'Table1',
    'Table1'[Shared Column] IN VALUES(SharedTable[Shared Column])
)

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[trevordunham]

Thanks @Kishore_KVN 

This does not seem to be working. Your suggested table is what I initially attempted but the filter expression does not make sense to me. This only filters table 1, I need column that works for both tables. I also am getting the following error when attemptint to create your filter expression: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.