The Power BI report that shows correct numbers when grouped by product but incorrect when grouped by customer is usually not a DAX formula issue. The cause lies one layer deeper: the filter does not reach the fact table, or it reaches it through an unintended path.
Dimensions filter, facts aggregate
In star schema design, model tables serve two roles. Dimension tables store entities such as products, customers, and dates; their task is to filter and group. Fact tables store events such as sales transactions; their task is to summarize.
What is often overlooked: there is no property to check to mark a table as either a dimension or a fact. What determines this is the cardinality of the relationship. In a one-to-many relationship, the "one" side is always the dimension table and the "many" side is always the fact table. Therefore, a large export table that mixes product attributes and transaction values in one row does not provide any filter propagation path.
Microsoft documentation recommends that each model table serves either as a dimension or a fact, not mixed, and that fact tables are always loaded at a consistent level of detail.
The cost of snowflake schema
If the data source is already normalized in layers, for example, product, subcategory, and then category in three separate tables, you may replicate that structure in the model. Microsoft openly notes four costs: more tables loaded, increasing model size, longer filter propagation chains, a busier Data pane for report builders, and hierarchies that cannot be formed from columns across tables. Generally, combining them into a single dimension table is more beneficial.
Only one active relationship between two tables
Date tables often need to be used for order date, shipping date, and receipt date simultaneously. You may create three relationships between the date table and the fact table, but only one can be active. The others are inactive and can only be called using the USERELATIONSHIP function within a measure.
The practical consequence is concrete: with one active path, you cannot create a visual that plots sales based on order date alongside sales based on shipping date. A common workaround is to create separate dimension tables for each role, then name the columns distinctly, such as Shipping Year instead of Year.
Two-way filters: limit, don’t make a habit
Microsoft's guidelines clearly state: minimize two-way relationships, as they add query processing load and can confuse report readers. There are three circumstances that justify them:
- One-to-one relationships, which are always two-way and cannot be set otherwise.
- Many-to-many relationships between two dimension tables, which require a bridge table and one two-way relationship for filters to penetrate.
- Analysis between dimensions, where the fact table is treated as a bridge.
For the most common need, which is slicers that only display options with data, there is a cheaper way: create a summing measure, then apply a visual filter on the slicer with a non-empty condition. The result is the same without adding a two-way relationship. If two-way is indeed necessary, activate it within the measure using the CROSSFILTER function, not in the relationship properties.
Do not connect two fact tables directly
Connecting the orders table and the shipping table directly with a many-to-many cardinality may seem practical. However, two consequences are not visible on the screen: visuals can only filter or group through the key column in one table, and if the data has integrity issues, some rows may be missing from the query results because such relationships are treated as limited relationships. Adding a dimension table alongside and then connecting both with a one-to-many relationship requires more initial work, but data errors surface rather than remain hidden.
Quick checklist
- Each table serves a single role: dimension or fact.
- All relationships should ideally be one-to-many, with single filter direction.
- Each dimension table has one unique identifier column.
- Two-way relationships are only for the three cases above, and their count is limited.
- Fact tables are loaded at a consistent level of detail.
Sources
- Microsoft Learn, Understand star schema and the importance for Power BI, https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
- Microsoft Learn, Bi-directional relationship guidance, https://learn.microsoft.com/en-us/power-bi/guidance/relationships-bidirectional-filtering
- Microsoft Learn, Many-to-many relationship guidance, https://learn.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many