Forum Discussion

rhl94's avatar
rhl94
Advocate III
5 years ago

Filter 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 rel...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
rhl94
Advocate III
5 years ago

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.