Filter two tables on Multiple Criteria

[aallman]

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.

[v-menakakota]

Hi @aallman ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-menakakota]

Hi @aallman  ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-menakakota]

Hi @aallman  ,

I hope the provided information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you

[lbendlin]

 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.

[sanalytics]

@lbendlin  That is a great reply..I must say.😀😁

Thank you

[aallman]

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]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[aallman]

Fail Table Example:

RefID	MfgID	Customer	Price	FailDate	Product	Department	Scale	Purification
1	10	CustA	$1	1/2/25	ProdA	FRP	1	HPLC
2	20	CustB	$2	1/3/25	ProdB	CCM	0.5	None
1	11	CustA	$1	1/4/25	ProdA	FRP	1	HPLC

Synthesis Table Example:

RefID	MfgID	Customer	SynthDate	Product	Department	Scale	Purification
1	10	CustA	1/1/25	ProdA	FRP	1	HPLC
2	20	CustB	1/1/25	ProdB	CCM	0.5	None
3	30	CustC	1/2/25	ProdC	FRP	1	None
1	11	CustA	1/2/25	ProdA	FRP	1	HPLC
2	21	CustB	1/2/25	ProdB	CCM	0.5	None

DateFilter Table: (related to both main tables one to many)

Date

1/1/25

1/2/25

1/3/25

1/4/25

Department Filter table: related to both main tables one to many

Department

FRP

CCM

TNF

MEASURES:

FailCount = distinctcount(FailTable[MfgID])

SynthCount = calculate(distinctcount(SynthesisTable[MfgID]),ALLSELECTED())

FailRate = FailCount/SynthCount

What I want is the visual filters on the page for the remaining columns (Customer, Purification, Scale, etc.) to filter BOTH tables/measures to get accurate fail rates.

For example, if I filter to the day 1/2/25 I get a fail rate of 33% (1/3). However, how it is set up now, if I filter to purification HPLC on day 1/2/25, I still get 33% instead of the 100% I need to see. Because my visual filters are just the column from the fail table. 

Since there are so many more fields than I have even shown, I am not sure if it is efficient or correct to create bridge tables (similar to my date and department filter tables) for EVERY field?

I hope that is what you needed to see. 

[lbendlin]

This would be the ideal data model

but you say 

Is it possible to make this method work where any filter the user applies will affect BOTH tables and therefore give an accurate rate?

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.  (It's more of a QlikSense thing).

You can solve that with bidirectional relationships as long as you do not introduce circular references.  If you hit those then you would need to consider using USERELATIONSHIP  and/or CROSSFILTER  where you can temporarily (for a measure) modify the data model connection characteristics.

The Purification filter should come from a dimension table, not from your fact tables.