Creating a Filter of the same column from different queries

[acamar22]

Hi Everyone

I'm having some trouble with a problem I can't seem to solve. I am creating a dashboard of failure rate: A bar/line graph with production numbers(Prodution Table) vs failure numbers(Failure Table).

I want to add a filter to search by Product ID. However, there is a Product ID column for both different tables. While trying to solve this, I've created a "Product ID Master Table" by appending the tables and finding unique values. (similar to a master Date Table), However, this doesnt seem to work because it is unresponsive to other filters. For example, if I switch the date filter, the new Product ID filter won't update with it. There will still be ID's from earlier dates that should've been filtered out.

How can I solve this? I would prefer to not combine these tables into 1. I've added a visual:

[Anonymous]

Hi @acamar22 

You can try the following method:

Use the IF function to determine if the Product ID is included in the range of the date filter, with a 1 for yes and a 0 for no, and then put that measure into the visual-level filters of the Product ID filter.

You can refer to the following documentation to do so.

Solved: Dynamic Summarize/Filter Formula - Microsoft Fabric Community

If the problem still exists, please provide sample data: How to provide sample data in the Power BI Forum - Microsoft Fabric Community. Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account. We can better understand the problem and help you.

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.

[Bipin-Lala]

Hi @acamar22,

Can you try creating a bi-directional relationship between the Product ID Master Table and Production Table using the Product ID field? Create the same relationship with the Failure table. This should help you pass the filter value to the new master table and thereby slicers should get filtered.

I have made some assumptions about your data model while suggesting this, so could you provide some more details about your tables/ fields if the above suggestion doesn't work?

[acamar22]

Hi, thanks for your help. It only lets me create a single bi-directional relationship per filter.

I suspect this is a relationship issue. I'd like to filter by Date, Product ID, and PC or MAC. However, I can't figure out the relationships. They are not interacting nicely. Can you please take a look at this and give me any advice?

My strategy is to make a master list of unique values found in the similar columns (highlighted)

Then I use those master lists on a slicer. How can I make this work? Is this usually the correct strategy when trying to compare 2 similar tables? I would like to keep them seperate

Thanks a lot for any help

[Bipin-Lala]

Hi @acamar22,

I believe you can overcome your challenge by creating a 3rd table by appending the Production and Failure table in the query editor for the columns Product ID, Production Date, and PC or Mac. Let's call it the Master table.

Then, use Product ID to create relationships between Master table and Production table and same with Failure table.

You should then be able to use the columns of the Master table as slicer fields and filter the respective values.

Let me know how it goes!

[acamar22]

Hi again@Bipin-Lala 

Thanks for the suggestion. I gave it a try and it doesnt seem to be working. The "Master Table" contains duplicate values, and a many-to-many relationship is forced for both relationships. This corrupts the data. I tried to clear duplicates to form a "many-to-one" relationship but I lose data along the way.

This is why I originally did this with 3 seperate queries, rather than a master table