Power BI / Basic: analytical and modeling foundations / Lesson 4
Star schema and filter paths
Build a small fact-and-dimension model with explicit grain, unique keys and deliberate filter propagation instead of relying on automatic relationships.
On this page
Lesson goals
- Assign fact and dimension responsibilities.
- Validate one-side uniqueness and unmatched fact keys.
- Explain direction and active relationship assumptions.
Declare consistent fact grain
A fact table stores observations at a consistent grain, such as one sales line. Dimensions describe entities used for filtering and grouping, such as product and date. Mixing monthly targets with individual order lines in one fact table can produce repeated targets and misleading totals. Separate those observations or define an explicit allocation contract. In the course model, FactSales has a ProductKey for each line and DimProduct has one row per product. The relationship communicates a filter path; it is not a database constraint that cleans invalid source records or guarantees referential integrity.
Read the filter diagram
The first diagram assumes an active one-to-many relationship with single-direction filtering from DimProduct to FactSales. Selecting product A restricts included sales rows and should yield revenue50 in the three-line fixture. This requires matching key values and types. A relationship created on a product description can fail when names change or are reused. Prefer a documented stable key. Do not turn on bidirectional filtering merely because a slicer seems unresponsive: it can introduce ambiguous paths and unintended interactions. First identify which table owns the filter and which facts it should constrain.
DimProduct[ProductKey] (one, unique)
-> active single-direction filter
FactSales[ProductKey] (many, repeated per sales line)
DimDate[Date] (one) -> FactSales[OrderDate] (many)The diagram is not a model file. It specifies assumptions that an actual Power BI relationship review must verify.
Check keys before visuals
The one side must satisfy its uniqueness contract. Duplicate dimension keys can force the wrong cardinality or prevent a relationship from being created as intended. Unmatched fact keys can surface as unknown or blank members and distort category interpretation. The second program checks a synthetic product dimension and reports a missing key C. It is not the relationship engine. Treat an unmatched key as a data-quality decision: repair the source, map to a documented unknown member or reject the row with an audit count. Do not delete it silently because a blank category looks untidy.
dimension_keys = ["A", "B"]
fact_keys = ["A", "B", "C", "A"]
print(len(dimension_keys) == len(set(dimension_keys)))
print(sorted(set(fact_keys) - set(dimension_keys)))True
['C']True confirms dimension uniqueness in the fixture; C is unmatched. No model relationship is created.
Handle multiple business dates
OrderDate and ShipDate answer different questions. A model can use separate role-playing date dimensions or an inactive relationship activated deliberately by a measure. The choice affects report usability and calculation design. Simply drawing both relationships does not make every visual know which date meaning is intended. Name measures and slicers with their business role, and verify both against a small order whose order and shipment dates differ. Keep timestamp-to-date conversion explicit as well; a midnight date key will not match an arbitrary time component without the intended preparation.
Review the small model contract
A model review should list each tableโs grain, key, relationship cardinality, direction, active state and unmatched-row policy. Verify default totals and at least one dimension filter before adding complex DAX. If a category repeats the grand total, inspect the filter path rather than changing the aggregation at random. All relationship diagrams here remain unexecuted and no Desktop model is opened. The key audit checks in-memory sets only. Actual engine evaluation, blank-member behavior, security propagation and report context require separately recorded Power BI validation before the model supports a business claim.
Common errors and fixes
Every product displays the same grand total
- Why it happens
- The intended filter has no valid active path to facts.
- How to fix it
- Inspect keys, types, active relationship and filter direction.
- Prevent it next time
- Keep a single-product expected-result case.
Duplicate product keys are accepted as a clean dimension
- Why it happens
- The one-side uniqueness contract was not audited.
- How to fix it
- Resolve entity versions or create a deliberate dimensional key design.
- Prevent it next time
- Check uniqueness before relationship creation.
Bidirectional filtering is enabled as a universal fix
- Why it happens
- A symptom was addressed without mapping intended propagation.
- How to fix it
- Design the required path and remove ambiguity deliberately.
- Prevent it next time
- Review filter behavior with a small model before extending it.
Check your understanding
1. Why should a monthly target not repeat on every daily sale line?
Show worked solution
The target has month-level grain while sales lines have finer grain. Repetition followed by summation inflates the target according to line count. Keep a separate target fact with shared dimensions or define an explicit allocation rule, then test totals at month and product context rather than assuming a flat join preserves meaning.
2. What should happen when fact key C has no product dimension row?
Show worked solution
The data contract must decide whether to repair, map to a documented unknown member or reject with a count. Preserve its revenue in reconciliation so the decision is visible. A visual blank is evidence of a potential model-quality issue, not permission to hide the category without understanding the affected facts.
Key takeaways
- Relationships propagate filters; they do not repair source integrity.
- Consistent grain and unique dimension keys make totals interpretable.