How to restrict the records in Table A using a condition in Table B, both are not directly associated.

[Koti_Ch]

Hi,

Please check the below image for the data model.

Requirement: Every Project has multiple ABS, for closed projects we shouldn't show the ABS data. for e.g in a Pivot table, if we click on a project name, it expands and shows a list of abs under it (like subtasks).

So here, the Fact table is connecting both ABS and Project Master tables. 

If Project Status = 'Closed', we should not show ABS data (or we should show N/A).

Please assist.

Thanks in advance.

Koteswar

[chris_djih]

Do you want the data not be shown (but actually data is in the app for other charts).
Or do you want the data is even not loaded if the ProjectStatus = closed?

[Koti_Ch]

I want the data not to be loaded if the ProjectStatus = Closed.

[chris_djih]

then youshould start with loading  ProjectMaster table and adding this where statement to the load statement: where [Project Status] <> 'closed'; //only load Projects that aren't closed.

then you fill your fact table and add the following exists statement:
where exists(project_natural_key)
and at last you load your ABS with: where exists (abs_natural_key)

[Koti_Ch]

Thank you, Chris.
This is very helpful.