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