Back to All Engineering Notes
Architecture•9 min read•2026-01-10

7 Common Semantic Model Problems (and How to Fix Them)

From bidirectional relationship cascades to auto date/time table bloat: the architectural anti-patterns hurting enterprise models.

Core Engineering Takeaways

  • •Snowflake dimension normalization causes needless table joins and slows down VertiPaq decompression.
  • •Many-to-many relationships through bridge tables introduce ambiguous filter propagation and silent double-counting.
  • •Role-playing dimensions should be modeled using inactive relationships and USERELATIONSHIP, not duplicated dimension tables.

1. Snowflake Schemas in Power BI

Relational database developers often normalize dimension tables (e.g. Product → SubCategory → Category). In Power BI's columnar VertiPaq engine, normalization is an anti-pattern. Every additional table hop requires the engine to traverse relationship pointer tables. Fix: Flatten all dimension hierarchies into a single star-schema dimension table during Power Query or SQL view staging.

2. Bidirectional Relationship Cascades

Enabling bidirectional (Both) cross-filtering seems like an easy fix when a visual slicer doesn't filter another slicer. But bidirectional filters create circular filter propagation loops, leading to wrong calculation totals and catastrophic query degradation. Fix: Use single direction (1:*) and implement CALCULATETABLE or CROSSFILTER in explicit DAX measures.

3. Inactive Role-Playing Duplication

When a Sales table has OrderDate, ShipDate, and DueDate, developers frequently copy the Date table three times (Order Date, Ship Date, Due Date). This triples memory overhead and prevents cross-metric analysis. Fix: Use a single canonical Date table with inactive relationships activated inside DAX via USERELATIONSHIP.

4. Calculated Columns in Fact Tables

Writing DAX calculated columns inside a 50-million-row fact table wastes memory and defeats VertiPaq compression because calculated columns are computed post-compression without optimal dictionary sorting. Fix: Compute row-level attributes upstream in SQL or Power Query.

5. Many-to-Many Bridges That Double-Count

A bridge table between Customer and Account (one customer, many accounts; one account, many customers) is a legitimate design. The problem is what happens to totals: a measure summed across the bridge counts a fact once for every path it can reach, so a sale linked to a joint account appears under both customers and the grand total no longer equals the sum of its parts.

Many-to-many relationships also need bidirectional filtering from the bridge to the dimension, which brings the propagation problems of problem 2 with it.

Fix: Decide explicitly whether a double-counted subtotal is the correct business answer (often it is — "sales influenced by this customer"). If not, allocate with a weighting factor on the bridge, or restrict the bidirectional filter to the measures that need it with CROSSFILTER inside CALCULATE instead of on the relationship. Document the behaviour in the measure description either way.

6. Auto Date/Time Table Bloat

Power BI Desktop's Auto Date/Time option creates a hidden date table for every date column in the model. A model with twenty date columns — including technical ones like LoadTimestamp — carries twenty hidden tables, each spanning the full range of its column, often from 1900 to 2099 if a placeholder date sneaks in.

Those tables cost memory, slow refresh and produce date hierarchies that cannot be shared across facts, so time intelligence behaves differently depending on which date column a visual uses.

Fix: Turn off Auto Date/Time (globally in Options and per file), add one proper Date table marked as a date table, relate it to the fact tables, and write time intelligence against it. Use inactive relationships plus USERELATIONSHIP for the secondary dates.

7. High-Cardinality Columns Nobody Uses

VertiPaq compresses each column with a dictionary of its distinct values. A column with millions of unique values — a GUID, a transaction ID, a datetime stamp to the second — barely compresses and can dominate the model's memory on its own. These columns are usually imported "just in case" and never used in a visual or relationship.

Fix: Run VertiPaq Analyzer and sort columns by size. Remove IDs nobody reports on, split datetime stamps into a date column and (if needed) an hour or minute column, and round decimals to the precision the business needs. Removing a single unused high-cardinality column often shrinks a model by a third or more.

Power BI Diagnostic Audit

Suspect architectural debt in your model?

Start with the free Model Health Snapshot, or get a fixed-scope Health Check with a prioritized remediation plan.

Get the next Engineering Note

Practical DAX, semantic model and Fabric notes. 1–2 a month, unsubscribe any time.

1–2 useful notes a month. Unsubscribe at any time.

Piyush JainPower BI & Fabric Engineer
#Architecture#Star Schema#Relationships#DAX#Governance