Forum Discussion

jack3's avatar
jack3
Microsoft Employee
6 years ago
Solved

Filter two separate data sets using one filter

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 ta...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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's avatar
Pragati11
Super User
6 years ago

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