Rescuing an Overgrown Semantic Model with Slow DAX
Fabrikam has one semantic model with a fact table of 800 million rows and 12 dimension tables. It contains 45 measures, 180 calculated columns, 22 bi-directional relationships, and several DISTINCTCOUNT measures over customer and product keys. A report page with eight visuals takes 45 seconds to render, and capacity CPU spikes during morning usage. Users need same-day data but not real-time. The model was built incrementally by four developers, and no one can explain why most relationships are bi-directional. The source is a Fabric lakehouse with clean Delta tables.
1. Diagnose the likely causes of the slow report and prioritize fixes.
Read the worked answer
The most likely causes are relationship direction and model shape, not one bad visual. Twenty-two bi-directional relationships create ambiguous filter paths and force the engine to evaluate extra joins; convert them to single-direction unless a specific many-to-many requirement is proven. One hundred eighty calculated columns inflate the model and are often computed row by row at query time; move them upstream into the Delta tables or Power Query where possible. DISTINCTCOUNT over high-cardinality keys is expensive; check whether the same counts can be pre-aggregated or replaced with a bridge table.
Prioritize in this order: remove unnecessary bi-directional relationships, remove unused columns and measures, move calculated columns upstream, then address the DISTINCTCOUNT measures. Fixing the star schema first usually reduces the need to rewrite DAX.
2. How would you redesign the measure layer and reduce the cost of the DISTINCTCOUNT measures?
Read the worked answer
Keep base measures simple and build variations with CALCULATE and variables rather than repeated expressions. Avoid iterators over the 800-million-row fact table when a set-based aggregate is possible. For DISTINCTCOUNT, first confirm the business definition: distinct customers who purchased, distinct products sold, or distinct combinations. If the grain allows, create an aggregate table at customer-day or product-day grain and count there; otherwise use a bridge table that stores the distinct pairs and count the bridge.
Use variables to evaluate filters once, and avoid measures that reference other measures in nested iterators without need. Validate each rewritten measure against the original for a known slice of data, because performance fixes that change results are not fixes.
3. The team wants to add an aggregation table but is unsure whether it will be used. What is your decision framework?
Read the worked answer
Use an aggregation table only when the query pattern is predictable and the grain is materially smaller than the fact table. Identify the top report pages and the common group-by columns, such as date, region, and product category. Build an aggregate table at that grain, map it in the model, and verify that Power BI chooses it for the targeted visuals using Performance Analyzer or query traces.
Do not add aggregations blindly: every aggregate adds refresh and model complexity. If the report still scans 800 million rows, the aggregation is not being hit, usually because the query filters on a column not present at the aggregate grain or because the relationship is wrong. Start with one high-value aggregation, prove hit rate, then expand. For same-day data, keep the aggregate refresh aligned with the Delta update cadence.