Forum Discussion
Helper IIFilter two tables on Multiple Criteria
Helper IIFail 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.
Super UserThis 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.