Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
jack3
Microsoft Employee
Microsoft Employee

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?

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

@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

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Pragati11
Super User
Super User

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.