Forum Discussion
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 tables.
However, I want to filter them on common columns that they both have, Market, Product, etc. Is there any way of doing this?
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
3 Replies
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
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
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 ColumnsRegards,
Xiaoxin Sheng
