Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Model Issue: Combining two Fact Tables

Hi together,

I have an issue with my report and I guess the reason is within the model architecture.

This is an extract of my model:


I have two dimension tables, Product and Region.
In the first Fact-Table I have a ratio between those tables. So basically a percentage value, how the Product-Share is within the regions. (many-to-many combinations possible)

Based on that I want to filter my Sales data within the FactSales table. (based on the product key out of the first FactTable)

I would like to be able to filter the sales data from both dimensions, depending on the ratio.

Is there another way to do that? I thought of integrating the share directly within the FactSales table. But I need the share also within other fact tables so I want to define it centralized in one spot.

Thank you in advance.

Regards,

Andrears


Need Help

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      This model worked best for my use-case.
      Thanks a lot!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    Thank you for your reply, mickey64 . Here l have another idea in mind. Please check if there is anything that can be improved. Example data can be viewed from the attached .pbix file.Here is my solution:

     

    Method1

     

    1.Merge in the Power Query Editor:

     

     

    2. Select the two fact tables to create a merged table:

    3.Select Sales[$] to expand:

     

     

    4.The model is shown in the figure:

     

     

    Method2

     

    1.Create a bridge table:

     

    Bridge Table = SUMMARIZE('FACTProductRegionRatio','FACTProductRegionRatio'[Product Key],'FACTProductRegionRatio'[Region Key])

     

    2.The model is shown in the figure:

     

     

    Both of these methods can be able to filter the sales data from both dimensions:

     

    Best Regards,
    Zhu
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!