Modelling Multiple Fact Tables

[cmncp]

I am trying to model 2 fact tables that are related to a couple of common dimensions.

I have the following tables:

Projects

This is a dimension table that lists various construction projects.  Key is ObjectNumber

Project Costs

This is a fact table and contains a list of all costs associated with various construction projects over time.

Assets

Dimension table that contains various Assets.  Each Project has one or more Assets associated with it

Asset Values

Fact table that contains asset valuations over the years

ProjectAssets

This is a bridging table that links the Projects and Assets tables

Calendar

Standard Calendar hierarchy

This is how I have currently modelled the relationships (red border = fact table, green = dimension, orange = bridging)

 

I have tried to model this many different ways, but each time the project costs are not being returned correctly for the assets.  In the following scrren shot, "C0009" is the Project code and the numbers under it are Asset codes.  In reality, C0009 only has 1 asset, but it is listing all of them, with the whole project cost repeated.

 

 Any help is greatly appreciated.

[v-shex-msft]

Hi  @cmncp,

According to your screenshot, it seems like the filter function based on related ship not works.

I'd like to suggest you modify your cross filter direction to "both".(single mode only available on one side e.g: A can filter B, B can't filter A)

If above not help, can you please share the sample file to test?

Regards,

Xiaoxin Sheng

[Greg_Deckler]

Tough to say but I suspect that you need to edit your cross filter directions on your relationships.

[cmncp]

I've tried different settings, but haven't found anything that works.