Power BI star schema: grain, relationships, and reliable measures

Define the grain before writing the measure

Grain describes exactly what one row represents. In an illustrative service-case model, a fact table could hold one row per case at creation. Another table might hold one row per case per daily snapshot. Those are different grains, even if both contain a case identifier.

Counting rows in the snapshot table counts case-days, not unique cases. Adding a monthly target to every daily row can multiply the target. Write the grain in the documentation and verify that every imported record follows it. If two processes need different grains, model them separately and deliberately.

Separate facts from the attributes that explain them

Microsoft describes facts as the basis for summarization and dimensions as the basis for filtering and grouping. This conceptual design keeps those responsibilities visible:

This is a design example, not a universal schema. A team reassignment or reopened case may require separate event history. Decide how those events should appear before choosing between current-state and historical reporting.

Check keys and relationship behavior explicitly

The dimension's relationship key must identify a single row on the one side. Duplicate team keys, unmatched dates, and mismatched data types deserve investigation before release. Do not silently discard unmatched facts to make a reconciliation pass.

After creating a relationship, compare the unfiltered total with the total grouped by its dimension. Document unknown categories. Test a filter on one team, two teams, and an empty team. More relationships or broader filter propagation do not automatically produce a better model; use the behavior the reporting requirement actually needs.

Make business measures explicit

If FactCase truly contains one row per case, a simple illustrative measure could be:

Case Count = COUNTROWS(FactCase)

If the source contains multiple events per case, that formula answers a different question. A distinct case count may be appropriate, but it should not disguise an unintended join that duplicated records. Fix the model's meaning first, then choose the calculation.

Keep a small test set with expected totals by date, team, and status. When a definition changes, update the examples and rerun the checks. Results should follow documented rules, rather than depend on an analyst remembering which visual uses a special formula.

Decide what historical labels should mean

If a team moves to another region, should last year's cases show its old region or its current region? Both can be legitimate questions. They require different history handling, and the answer should come from the owner rather than a convenient overwrite.

Before scaling, resolve grain, key uniqueness, unmatched records, and historical meaning. Our Power BI planning guide turns those decisions into a brief. AVIZ DATA can help scope the model and reporting implementation around your team's decisions.