Forum Discussion
Advocate IIIFilter fact based on another fact
Hi
I have a star schema model with multiple fact tables. I want fact table 1 to be filtered by fact table 2.
Model is as follows: dCompany, dCoverage, fPolicies, fCoverages.
fPolicies relates to dCompany and fCoverages relates to dCompany and dCoverage.
A policy can have multiple coverage. When selecting a company, I want to return all rows from fPolicies that have the same coverage as the selected company.
E.g. Company A have Coverage X, Y and Z. I want to return all rows from fPolicies that also have X, Y and Z coverage.
4 Replies
How is a policy associated with a coverage? Since fCoverages is related to dCompany and dCompany isrelated to fPolicies all you can do is say that policies are associated with coverages through a company. But then, all policies under the company will be associated with ALL coverages under this company. I don't think this is what you would want. You are missing a link between a policy and its coverages. Probably a bridge table.
rhl94All polices under a given company will have identical coverages, so that would be fine in this case.
EDIT: There is also a dPolicy table that links to both fact tables.
Rows from which table do you really want to see? Let's say you drop on your visual columns from dPolicy. Then you have a slicer set to some company name. Then you filter the rows of the table via Filter Pane using this measure:
[Should Display Policy?] = 1 - ISEMPTY( fPolicies )and the condition is WHEN [Should Display Policy?] = 1.
