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.
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”. 
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!
