All lessons

Module 3: Data modelling · Lesson 3.5 · about 15 min

Common modelling mistakes and how to fix them

Seven model problems that cause wrong numbers or slow reports, how to spot each one, and the fix, from duplicate keys and bidirectional filters to mixed grains and missing date tables.

Module 3 · about 15 minutes · read first: 3.2. You’ll be able to diagnose the most common model problems from their symptoms.

1. Why this matters

When a number is wrong, the cause is usually in the model, not the DAX. Spotting these patterns quickly is what makes a BI developer trusted.

2. Seven mistakes

Symptom Likely cause Fix
A slicer doesn’t change a visual Missing or inactive relationship Create it in Model view; check the key columns’ types match (text vs number)
Totals far too high Duplicate keys on the one side, or a merge that multiplied rows Remove duplicates on the key in Power Query; compare row counts
Targets about 30× too big A monthly table related to daily dates (mixed grain) Relate at the right grain, or compare at month level with TREATAS (Lesson 6.4)
Same visual shows blank category “(Blank)” Fact rows whose key isn’t in the dimension Left anti merge to list them; add the missing rows to the dimension or fix the key
Odd numbers when two slicers interact Several bidirectional relationships, ambiguous paths Set single direction; use DISTINCTCOUNT on the fact table, or CROSSFILTER in one measure
Year-to-date or last year wrong No proper date table, or using Sales[Date] on visuals Build and mark a date table (Lesson 3.3); use its columns everywhere
Huge file, slow refresh Unused columns, high-cardinality text (IDs, timestamps), many calculated columns, auto date/time Remove columns, split date and time, move columns to Power Query, turn off auto date/time

3. See the blank-category problem

(Blank) appearing in a visual is the engine telling you some fact rows have no matching dimension row. It adds a blank row to the dimension so the totals still add up. Never ignore it: it’s a data quality issue to report.

Principle

Principle: every wrong total breaks one of three promises: keys are unique on the one side, each table has one grain, and filters have one path. Check those three first.

Try it: match each fix in the table above to the promise it restores.

4. Worked example

“Our Region total for West is lower than the three West stores added up.” Check: the Store table has Harbour Front twice, once as “West” and once with a typo “Wset”. Store’s key is still unique (two different keys), but some Sales rows point to the typo row, so they show under “Wset”. Fix the region in the source (or Power Query: replace values), and the totals reconcile. Lesson: always compare a total with the sum of its parts.

5. Practice

  1. A relationship won’t create; Power BI says the columns have different data types. What do you do?
  2. A model has 40 calculated columns on a 10-million-row fact table and the file is 2 GB. Name two fixes.
  3. Why is a timestamp column (date and time to the second) bad for compression?
Answers
  1. Make both key columns the same type in Power Query (usually whole number or text).
  2. Turn columns that only feed measures into measures (SUMX); move the rest to Power Query or the source; remove unused ones.
  3. Almost every value is different (high cardinality), so the engine can’t compress it. Split it into a Date column and a Time column (or drop the time).

Check yourself

1Totals are too high after adding a product list. What is the first thing to check?
2Monthly targets are joined to every day in a month. What goes wrong?
3Which is usually the better fix for "products sold in a store"?

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. Northbay Goods is a made-up shop and its numbers are generated. Power BI changes every month: menu names can move, so check Microsoft Learn if a button is not where the lesson says. The practice editor runs in your browser; nothing you type is sent anywhere.