Filter two tables on Multiple Criteria

Question

Hello! this might be confusing and I can't share any data so I will do my best!

I have two tables: SynthesizedItems and Failures. I have a report page dedicated to fail rate. This is just the number of fails/number of synthesized items. (measures in the failure table)

Originally, I linked the two tables to a common date table. this allowed me to see the fail rate adjusted based on the users filtering by date. Then, I realized I needed to be able to break this down further. I added an additional table called DepartmentFilter and linked it to department in both the fail and synthesis tables. this seems to work.

Now my question. My report has SEVERAL factors involved that users can slice their data by (all coming from the failure table) and I noticed that if the user filters to a certain customer, the fail table will filter but not the synthesized items table. (makes sense because they are not related, although customer exists as a field in both tables). Is it possible to make this method work where any filter the user applies will affect BOTH tables and therefore give an accurate rate? Is there a limit to how many common "filter" tables I can create and link to both? Is there a way to do it other than creating tables for each field? really trying to avoid this since it would be about 10 fields.

lbendlin · Answer

I can't share any data
If you can't make the effort to prepare sample data then we can't make the effort to try and help.

lbendlin · Answer

This would be the ideal data model
&nbsp;

but you say&nbsp;
&nbsp;
&nbsp;
Is it possible to make this method work where any filter the user applies will affect BOTH tables and therefore give an accurate rate?
&nbsp;
&nbsp;
&nbsp;
This would require the fact table to control the dimension table. Let's say you want to show Synthesis data for your fail dates

That is possible, but it is not what Power BI is about.&nbsp; (It's more of a QlikSense thing).
&nbsp;
You can solve that with bidirectional relationships as long as you do not introduce circular references.&nbsp; If you hit those then you would need to consider using USERELATIONSHIP&nbsp; and/or CROSSFILTER&nbsp; where you can temporarily (for a measure) modify the data model connection characteristics.
&nbsp;
The Purification filter should come from a dimension table, not from your fact tables.

&nbsp;
&nbsp;

aallman · Answer

I can show you how the two tables are set up?

In both tables are the fields: RefID, Customer, Price, Date, Product, Department, Services, Scale,Purification.

Right now, I have the two filter tables (date and department) related to both the fail and synthesis tables.

What would you need to try to help?

lbendlin · Answer

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

sanalytics · Answer

lbendlin&nbsp; That is a great reply..I must say.😀😁Thank you

Forum Discussion

Filter two tables on Multiple Criteria

9 Replies

Related content

DAX FILTER with multiple criteria

Multiple Criteria Filter

Dynamic Filter creation based on multiple criteria

Sequential Order column based on multiple criteria

Filter Multiple Criteria