Filter fact based on another fact

[rhl94]

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.

[Anonymous]

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.

[rhl94]

All 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.

[Anonymous]

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.

[rhl94]

I want to return all rows with same coverage as the filtered company. I'm aware of the Check Emplty Table COndition by SQLBI, but it does not seem to solve the issue.

We have 100 policies. Company A have 10 policies.

We have 50 policies with identical coverage as Company A. I want the measure to return 50 when filtering Company A.

We count policies in fPolicies and we can see the coverage in dCoverage which is related to fCoverages, but not fPolicies. dCompany and dPolicy are both conformed dimension that relates to both facts.