Forum Discussion
Helper VPoor performance with flaked dimension
Hello Community,
I have a Product table that filters my fact table one to many. On a customer request, I've added a new table with some custom segments, these segments consists of some product groupings the client work with. Multiple products can belong to the same segment/grouping.
Currently, the Custom segment table is connected to the product table one to many with both way filtering and the product table then filters the fact table one to many. This setup is really slow and is leading to poor performance.
If a product could only belong to one segment, it would be easy to just merge it onto the existing product table in PQ and have a column for it, but since a product can belong to multiple segments, I am unsure how to implement this in a performant way.
Connecting the custom segments table to the fact table directly is better performance even though it's many to many but then selections made in either product or custom no longer filters the other.
I am hoping to get some ideas on a way to implement something like this that is still good performance and perhaps just connecting both table directly to the fact table is a better trade off (performance over slicer filtering) but maybe one of you experienced experts have a better idea for me
1 Reply
Hi BIanon ,
Here are some possible solutions that you can try:
- Use a bridge table to connect the custom segments table and the product table. A bridge table is a table that contains the combinations of segment and product keys, and acts as an intermediary between the two tables. This way, you can avoid using bidirectional filtering and reduce the number of rows in the bridge table.
- Use DAX measures to calculate the custom segments dynamically. Instead of creating a separate table for the custom segments, you can write some DAX measures that use logic or parameters to assign products to different segments based on the user’s selection. This way, you can avoid creating additional tables and relationships, and improve the performance of your report.
Optimization guide for Power BI - Power BI | Microsoft Learn
How To Create Dynamic & Custom Groupings For Your ... - Microsoft Fabric Community
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
