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.
