Using multiple shared columns as filters for two tables at once

[trevordunham]

I have 3 shared columns between two data tables. I need to keep the data tables separate as they are different data as well as separate API connections. 

For discussion, we will call the tables: Table 1 and Table 2, and the shared columns, SharedColumn1, SharedColumn2 and SharedColumn3.

I was able to create a table to use SharedColumn1 as a filter for Table 1 and Table 2 like so:

SharedTable1=

DISTINCT(UNION(

  DISTINCT('Table1'[SharedColumn1]),  DISTINCT('Table2'[SharedColumn1])))

This returns a column I renamed SharedTableColumn1

I then created relationships between SharedTable1, Table1 and Table2 with the corresponding shared column. I was able to filter my two tables using SharedTableColumn1.

I then tried to do the same thing with SharedColumn2 as so:

SharedTable2=

DISTINCT(UNION(

  DISTINCT('Table1'[SharedColumn2]),  DISTINCT('Table2'[SharedColumn2])))

However when I tried to create relationships between the resulting column SharedTableColumn2 from SharedTable2 and Table1/Table2, I get an error due to ambiguity between Table 1 and Table 2.

Anyone know how to fix this or if there is a better method?

Thanks

[v-xuxinyi-msft]

Hi @trevordunham 

If I understand correctly, you want to create two relationships between two tables. In general, there can only be one active relationship between two tables. If there are multiple relationships, they need to be set to inactive. In addition, specific Cross-filter instructions may lead to errors.

The following documents are available for your reference:

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships#a... 

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships#w... 

You can use the following function to specify the relationship to be used in a particular calculation.

USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

Best Regards,
Yulia Xu

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

[trevordunham]

Thanks @v-xuxinyi-msft ,

I don't necessarily want to use multiple relationships, this was just the method I attempted for my problem. I ultimately just want to be able to filter my two separate tables by 3 shared columns between them.

The end result will be a page with two matrix visuals (one for each table) and 3 slicers at the top (the shared columns) that filter both matrix visuals at the same time.