Forum Discussion
Helper IIFilter two tables on Multiple Criteria
Super User I can't share any dataIf you can't make the effort to prepare sample data then we can't make the effort to try and help.
aallmanI 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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523- 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.
sanalyticslbendlin That is a great reply..I must say.😀😁
Thank you