Forum Discussion
Performance question
Hi zenisekd, unless you are using DirectQuery, having a separate table won’t improve slicer speed.
The performance of a slicer is mainly affected by the cardinality (the number of unique values) in the column, not the total number of rows in the fact table.
If there were additional attributes related to the "Product Code" (e.g., product name, category, price, etc.), then a dimension table would be beneficial for data modeling and easier filtering.
Hey there!
In Power BI, the best option would be using a dimension table for slicers instead of filtering directly from the fact table. However, the actual performance improvement depends on cardinality and model structure.
- If the "Product Code" is in the fact table, Power BI must scan through all rows to apply filters, which is inefficient.
- If "Product Code" is moved to a separate dimension table, Power BI processes fewer unique values, leading to better performance.
Also Power BI works best with a star schema rather than a flat table. A separate dimension table reduces memory usage and improves DAX calculations.
Your colleague is correct in my opinion —creating a dimension table for "Product Code" is the better approach in Power BI, especially when there are many unique product codes. It allows for better compression, filtering efficiency, and slicer performance.
Hope this helps!
😁😁
Creating a separate product code table wont create fewer unique values of product codes, it only introduces additional relation which comes with some overheads..