Filter two separate data sets using one filter

[jack3]

I have two data sets and I want to be able to filter them using one filter. Both data sets are seperate and there is no unique indentifier that can be used to create a relationship between the two tables.

However, I want to filter them on common columns that they both have, Market, Product, etc. Is there any way of doing this?

[v-shex-msft]

HI @jack3,

If your dataset relationship is based on multiple fields, you can also take a look at the following blog to know how to build a relationship between multiple columns:
Relationship in Power BI with Multiple Columns 

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

@jack3 , Do you have common tables for those, if not create common tables.

The first one should work ideally, But in the new version, it is not, so try one of the three. It gives circular dependency error

new table like

product = distinct(union(all(Dataset1[Product]),all(Dataset2[Product])))
product =distinct(union(distinct(Dataset1[Product]),distinct(Dataset2[Product])))
product = summarize(union(distinct(Dataset1[Product]),distinct(Dataset2[Product])),[Product])

Create for others too and join and the analyze with common dimesion

[Pragati11]

HI @jack3 ,

Let's take an example of PRODUCT column which exists in both of your tables.

Create a bridge table PRODUCT MAPPING, which will have a single column PRODUCT. This column will have distinct values of PRODUCT from both the tables.

For e.g. TABLE 1 has PRODUCT = P1, P2, P3

             TABLE 2 has PRODUCT = P4, P2, P5

             PRODUCT MAPPING will have PRODUCT = P1, P2, P3, P4, P5

Now use this PRODCUT MAPPING table to create relationship on PRODUCT column with TABLE 1 and TABLE 2 PRODUCT columns.

Now create a slicer. Move PRODUCT column from PRODUCT MAPPING  table to this slicer.

When you select anything here, it will filter both the tables of your's.

Thanks,

Pragati