2 fact tables shows all dimensions for 1 dim table

Hi all,

this is my first topic as my search on several forums did not solve my problem.

I have the datamodel below where I have 2 FACT tables and 3 DIMENSION tables where of 2 are linked to both fact tables, 1 (MD_GL) is only linked to 1 fact table (Fact_cost)

And I have now following issue: when I use data from the dimension table (MD_GL) and use data from the other FACT table (Fact_Volume), then I got a list of all the dimensions of the dimension table.

image

Measures
Volume Loaded
= CALCULATE([SumOfVolume];FILTER(Fact_Volume;Fact_Volume[Type] = “Loaded”))

Cost
= CALCULATE([SumOfValue];FILTER(Fact_Cost;Fact_Cost[GeneralLedgerAccountCode]<>“5220001200”);FILTER(Fact_Cost;Fact_Cost[GeneralLedgerAccountCode] <> “6460001100”))

UCPT_Loaded
= DIVIDE([Cost];DIVIDE([VolumeLoaded];1000))

Can someone help how I can solve this or do I have to create 1 FACT table or do I have the filter the dimension table (MD_GL) based on the content of the fact table (Fact_Cost) ?

Thanks already in advance,
Jan

Hi @Lofgren,

Welcome to the Forum!

The way your model is set up at the moment, there is no relationship (so no filtering coming) from MD_GL to Fact_Volume.

Generally there are two ways around this. First if both Fact_Cost and Fact_Volume share a common and unique identifier for each record then creating one fact table would be the way to go. Alternatively you could perhaps Merge Fact_Volume into Fact_Cost.
Second if you are able to identify the MD_GL Key belonging to each Fact_Volume record you can create a relationship between them.

I hope this is helpful.

Hi Melissa,

Thanks already for the prompt reply !! What a service !!

There is no relation between MD_GL and FACT_Volume and also no unique identifier between the 2 fact tables.
In the fact_volume table I have different types of volumes (loaded, supplied, sold, etc) that I use in combination with the cost (Fact_Cost table) to calculate a unit cost.

In the past I combined it in 1 Fact table but then my cost were duplicated or tripled (per volume type), so that was the reason I tried this Data Model.

Question, is it ‘easy’ to filter dynamicly the MD_GL table based on the GL_Code I have in my FACT_Cost table (are only max 10 GL codes)

If not I can ‘hardcode’ to limit the MD_GL table to only those 10 GL_Codes.

Regards,
Jan

The correct answer “it depends”. :wink:
Can you elaborate more on that dynamic filter requirement you’ve envisioned? Provide a small sample, scenario and desired outcome. Things tend to move a lot quicker then…

In case you need to mask you data.

Hi @Lofgren, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!