1. Inactive Relationships
What It Is:
Power Pivot allows only one active relationship between any two tables. Additional relationships become inactive and are not used automatically in visuals or measures.
How to Identify:
- In the model view, inactive relationships appear as dashed lines.
- Active relationships are shown as solid lines.
How to Fix:
- If the correct relationship is inactive, either:
- Make it active (delete any conflicting relationship first), or
- Use USERELATIONSHIP() in your DAX measures to activate it selectively:
Total Sales by Ship Date =
CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
2. Incorrect Cardinality (Many-to-Many, One-to-Many, and One-to-One)
What It Is:
Relationships must accurately represent the uniqueness of the data:
The most prevalent is one-to-many (1:*), in which the fact table contains duplicates, and the dimension table has unique keys.
Although many-to-many can be employed unless it is necessary, it is frequently an indication of subpar modeling.
How to Fix It: Verify that the key column in the lookup table (dimension) contains unique values.
If you need to remove duplicates, use DISTINCT in Power Query or DAX.
3. Incorrect Relationship Direction (Cross-filtering)
What It Is:
Relationships filter from dimension to fact tables (one → many) by default. You will need to modify the model if your visual relies on filtering in the opposite direction.
How to Fix It: Make the relationship's bi-directional cross-filtering active.
Or redesign the visual to respect the existing direction.
For advanced needs, use DAX with TREATAS() or virtual relationships.
4. Mismatch in Data Type
What It Is: Power Pivot won't relate column names even if they appear to be the same unless the data types match precisely.
Fix: Verify that both columns are, for instance, Text, Whole Number, or Date in Power Query or Data View.
Use Power Query's Change Type option to correct inconsistencies.
5. Missing or blank keys
What It Is: Rows in key columns that contain null or blank values won't be filtered because they won't be involved in relationships.
How to Fix It: In Power Query, eliminate or substitute blanks with a default value (such as "Unknown").
To find unmatched values, use EXCEPT() logic or anti-joins.
6. Relationship Path Filters
What It Is:
Related tables won't be impacted if a filter in the visual originates from a disconnected table (one that isn't connected by relationships).
How to Fix: Either establish a suitable relationship between the table and your model or
To model the relationship, use DAX functions such as TREATAS(), LOOKUPVALUE(), or RELATED().
7. Indeterminate Connections
What It Is: Power Pivot may be unclear about which of several possible relationship paths to use in another table.
How to Fix It: Make the model simpler by eliminating unnecessary paths.
To point DAX in the right direction, use USERELATIONSHIP().
Where feasible, flatten snowflake schemas into star schemas.
8. Confusion between Row Context and Filter Context
What It Is:
A metric that doesn't react to filters could be
How to Fix:
Wrap expressions in CALCULATE() to apply filters in the correct context.
Example:
Correct Total = CALCULATE(SUM(Orders[Amount]))
