Power BI Data Modeling: Star Schemas, Relationships, and Performance Patterns

A Power BI data model determines how fast your reports run, how intuitive they are to build, and how accurately they calculate metrics. The core pattern is the star schema: fact tables (transactions, events) surrounded by dimension tables (products, customers, dates). Relationships between tables control how filters propagate and how DAX measures aggregate. Microsoft renamed Power BI datasets to semantic models in November 2023, and this article uses the current name.

Getting the model right matters more than any visual design choice. A poorly shaped model makes reports slow and calculations error-prone, and DAX tuning can only partly make up for it. A well-structured star schema with proper relationships, efficient data types, and clear measures makes report building almost trivial - filters work as expected, calculations are correct, and performance stays fast as data grows.

Star Schema: The Foundation

A star schema separates data into two types of tables. Fact tables contain the measurable events - sales transactions, website visits, support tickets. They are tall (many rows) and narrow (few columns): mostly numeric values and foreign keys. Dimension tables contain the descriptive attributes - product names, customer segments, date labels. They are wide (many columns) and short (relatively few rows).

A concrete example: a Sales Fact table with columns DateKey, ProductKey, CustomerKey, Quantity, UnitPrice, and TotalAmount. Each key links to a dimension: the Date Dimension provides Year, Quarter, Month, DayOfWeek. The Product Dimension provides ProductName, Category, Subcategory, Brand. The Customer Dimension provides CustomerName, Segment, Region, City.

Star schemas suit Power BI better than flat tables, and Microsoft's star schema guidance recommends them for semantic models. Three reasons stand out. First, Power BI's VertiPaq engine compresses columns with few distinct values very well - a "Category" column with 20 unique values takes up very little space. Second, filter direction is clear - filters flow from dimensions to facts, never creating circular paths. Third, DAX measures perform best when they aggregate over a narrow fact table with integer keys, not when they scan wide tables with repeated text values.

The anti-pattern to avoid is the "one big table" - a single denormalized table with every column. It works for small data. As row counts grow, every descriptive value is repeated on every row, so the model takes more memory, DAX has to scan wider tables, and what filters what becomes harder to follow. Microsoft's guidance is to keep each model table either a dimension or a fact, not a mix of both.

Relationships and Filter Propagation

Relationships in Power BI define how filters move between tables. When a user selects "2025" in a date slicer, Power BI filters the Date Dimension to that year, then propagates the filter through the relationship to the Sales Fact table, showing only 2025 transactions.

The standard pattern is one-to-many: one row in the dimension matches many rows in the fact table. By default, the relationship's cross-filter direction is single: the filter flows from the "one" side (dimension) to the "many" side (fact), which keeps filter paths unambiguous.

Bi-directional filtering reverses the flow, letting fact tables filter dimensions. Example: selecting a product in a visual that's connected to Sales Fact, and having the Customer Dimension filter to show only customers who bought that product. It's powerful but risky: bi-directional relationships need more processing, which can slow queries, and they can make a model ambiguous when more than one filter path connects two tables. Where a single measure needs the reverse direction, Microsoft recommends enabling it inside that measure with the CROSSFILTER DAX function instead of on the relationship.

Role-playing dimensions occur when a fact table has multiple relationships to the same dimension. A sales fact might have both OrderDateKey and ShipDateKey, both pointing to the Date Dimension. Power BI only allows one active relationship per table pair. The second becomes inactive and must be activated explicitly in DAX using USERELATIONSHIP():

ShippedRevenue = CALCULATE(SUM(Sales[TotalAmount]), USERELATIONSHIP(Sales[ShipDateKey], DateDim[DateKey]))

Measures vs. Calculated Columns

This distinction trips up many Power BI beginners, and getting it wrong leads to bloated models and wrong calculations.

Calculated columns are computed once during data refresh and stored in the model. They operate in row context - each row gets a value based on that row's data. Use them for fixed categorizations that don't change with user filters: PriceBand = IF([ListPrice] > 100, "Premium", "Standard"). The value is the same regardless of what the user selects on the dashboard.

Measures are computed at query time in response to user interactions. They operate in filter context - the result changes based on which filters are active. TotalRevenue = SUM(Sales[TotalAmount]) returns a different number when the user selects "EMEA" versus "North America." Measures consume zero storage because they're formulas, not data.

The decision framework: if the value changes based on what the user selects (total revenue for selected region, year-over-year growth for selected product), use a measure. If the value is fixed per row regardless of filter context (categorizing products into price bands, extracting the domain from an email address), use a calculated column. When in doubt, use a measure - they're more memory-efficient and more flexible. And when a fixed per-row value can be computed before the data reaches the model, Microsoft's data reduction guidance prefers a custom column in Power Query (or in the source) over a DAX calculated column, because calculated columns typically compress less efficiently and are built only after all tables load.

Hierarchies and Drill-Down

Hierarchies enable drill-down navigation in visuals. A Date hierarchy (Year, Quarter, Month, Day) lets users start at the yearly view and drill into months to find trends. A Product hierarchy (Category, Subcategory, Product) lets users explore from broad categories to specific items. A Geography hierarchy (Country, State, City) supports regional analysis.

Build hierarchies in the model view by dragging columns into a hierarchy group. Always create a dedicated Date dimension table rather than relying on Power BI's auto date/time feature: its hidden tables use calendar years only, cannot add columns such as weeks, cannot filter several fact tables at once, and add to model size for every date column. Mark the table with Mark as Date Table so time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR work correctly.

Performance Optimization

Most performance problems in Power BI models trace back to a handful of causes. Five are worth checking first:

1. Text keys instead of integer keys. If your fact table joins to dimensions on text columns like product codes or customer IDs, switch to integer surrogate keys. VertiPaq stores numeric columns with value encoding and text with hash encoding, which needs a dictionary lookup; Microsoft's data reduction guidance notes that converting high-cardinality text to whole numbers can result in significant data reduction on large tables.

2. Unused columns. Every column in the model consumes memory and adds to refresh time - even columns that appear in no visual. Remove columns you don't need in Power Query before they reach the model. A common offender: importing every column of a wide source table when the report uses a dozen.

3. High-cardinality text columns in fact tables. A "Description" column with 5 million unique values in a fact table inflates model size dramatically. Move descriptive text to dimension tables, or remove it entirely if it's not used in analysis.

4. Calculated columns where measures would work. A calculated column that computes running totals for 50 million rows stores 50 million values. The equivalent measure computes the same result on the fly, using zero storage.

5. Missing aggregation tables. For very large fact tables kept in DirectQuery, create pre-aggregated summary tables in the source system (or in Databricks). Power BI's user-defined aggregations answer queries from the summary table, usually stored in Import mode, when its grain covers the query, and send the rest to the DirectQuery detail table. Summary queries can then return from memory instead of from the source.

Sizing limits: the default semantic model size limit is 1 GB, and shared capacity (Pro workspaces) hosts models up to that size. To grow beyond it, a model needs a Premium Per User workspace or a Premium or Fabric capacity with the large semantic model storage format enabled, where the limit follows the capacity size. VertiPaq compresses well, and Microsoft cites roughly 10x compression of source data, but large fact tables still benefit from incremental refresh, which is available with Pro too and refreshes only the most recent partitions.

Row-Level Security

Row-level security (RLS) restricts which data rows each user can see. A regional manager should see only their region's data. An account executive should see only their accounts. RLS makes this possible within a single shared report.

Implementation: create a security role in Power BI Desktop, define a DAX filter expression, and assign users to roles in Power BI Service. For a regional manager who should only see EMEA data:

[Region] = LOOKUPVALUE(Users[Region], Users[Email], USERPRINCIPALNAME())

Defined on a Region (or Geography) dimension table, this expression keeps only the current user's assigned region, and the filter then propagates through the one-to-many relationship to the Sales Fact table. Filters do not flow the other way by default: a rule defined on the fact table does not filter Product or Customer unless the relationship's Apply security filter in both directions option is enabled.

Test RLS in Power BI Desktop using View as Role before publishing, and with Test as role in the service afterwards. After publishing, assign Microsoft Entra ID users or security groups to each role on the semantic model's Security page. A common surprise: a Viewer who is not assigned to any role typically sees no data at all.

Advanced Patterns

Composite models combine Import and DirectQuery tables in a single model. Import your dimension tables for fast filtering and keep large fact tables in DirectQuery mode against Databricks. This gives you the best of both worlds: interactive dimension filtering with live fact data. Microsoft's composite model documentation covers the security and performance trade-offs.

Calculation groups eliminate repetitive time intelligence measures. Instead of creating separate YTD, QTD, and MTD versions of every measure (Revenue YTD, Cost YTD, Margin YTD...), define a calculation group with time intelligence items that apply to any measure. With 20 base measures, that is 20 measures plus one calculation group instead of 80 measures. Power BI Desktop creates calculation groups in Model view, and adding one turns on the Discourage implicit measures property, because calculation items apply only to explicit measures.

Many-to-many relationships through bridge tables handle cases where the standard one-to-many pattern doesn't fit. A customer who belongs to multiple segments, or a product that appears in multiple promotions, requires a bridge table to avoid duplicating rows. Design these carefully - incorrect bridge tables create inflated totals. Microsoft's guidance recommends exactly this bridging table when relating two dimensions, with a bi-directional filter across it so filters propagate.

Role-playing dimensions are the same dimension table used multiple times with different relationships. The Date Dimension connected to OrderDate (active) and ShipDate (inactive) is the classic example. Use USERELATIONSHIP() in measures that need the alternate relationship path. Microsoft's star schema guidance describes the alternative as a common technique: a separate copy of the dimension for each role (OrderDate table, ShipDate table), each with one active relationship, which also allows filtering by both dates at once. That matters if the model uses row-level security, because Microsoft warns that USERELATIONSHIP() with RLS enabled might cause unexpected errors.

How Dawiso Documents Your Data Model

Power BI data models are often tribal knowledge. The person who built the model knows that "Revenue" excludes returns, that "Active Customer" means a purchase in the last 90 days, and that the Customer Dimension gets refreshed from two different CRM systems. When that person leaves, the knowledge leaves too.

Dawiso's data catalog ingests Power BI metadata through the Power BI connector: semantic models (Datasets in the connector) with their columns and measures, including each measure's expression, plus reports, dashboards, dataflows, and datasources. The business glossary holds the terms, indicators, and rules behind them, with a data owner and data steward assigned, and glossary entries can be related to catalog objects. The definition of "Revenue" - gross vs. net, inclusion criteria, source of truth - lives in one place instead of in the model author's head.

Data lineage from the Power BI connector works at object level: a report uses a semantic model, the semantic model reads from a datasource, and the datasource connects through a gateway. When a source system changes - a CRM migration, a schema update, a new data feed - that chain shows which semantic models and reports sit downstream of the affected datasource. It does not trace individual columns, measures, or DAX expressions, so checking which measure a column change breaks remains a modeling task.

Through the Model Context Protocol (MCP), AI copilots can use the Dawiso MCP server to search the catalog, read an object's details, and follow its lineage. An analyst asking "how is customer lifetime value calculated?" can get the glossary definition and the documented Power BI measure, with its expression and the semantic model it lives in, without digging through a shared folder of outdated data dictionaries.

Conclusion

Power BI data modeling is an architecture discipline, not a configuration task. The star schema is the default that Microsoft's own guidance recommends for semantic models. Relationships should flow one direction (dimension to fact) unless you have a specific reason for bidirectional filtering. Measures beat calculated columns for anything that responds to user context. Performance optimization starts with data types and unused columns, not with DAX tuning. And the model's business logic - what "Revenue" means, where the data comes from, who owns it - belongs in a catalog, not in someone's head.

Sources

• Microsoft Learn, New name for Power BI datasets, November 2023.
• Microsoft Learn, Understand star schema and the importance for Power BI.
• Microsoft Learn, Bi-directional relationship guidance.
• SQLBI (Alberto Ferrari), Bidirectional relationships and ambiguity in DAX, November 2023.
• Microsoft Learn, Data reduction techniques for Import modeling.
• Microsoft Learn, Auto date/time guidance in Power BI Desktop.
• Microsoft Learn, User-defined aggregations.
• Microsoft Learn, Large semantic models in Power BI Premium.
• Microsoft Learn, Configure incremental refresh and real-time data for Power BI semantic models.
• Microsoft Learn, Row-level security (RLS) with Power BI.
• Microsoft Learn, Use composite models in Power BI Desktop.
• Microsoft Learn, Create calculation groups in Power BI.
• Dawiso Help, Power BI connector.
• Dawiso Help, Business Glossary.
• Dawiso Help, Role of ownership in data governance.
• Dawiso Help, Dawiso Model Context Protocol (MCP).