Ravindra BagaleCourses & study guides मराठी Track your progress

11. Data Modelling

Power BI Data Modelling Practice with Answer Key

By Last updated:

Suppose we are joining a new Nagpur store to the Blinkit Maharashtra model (fictional data). The store is in Orders but someone forgot to add it to DarkStore.

Orders

Order ID Store ID Order Date Amount (₹)
O1 BLK-PUN-01 01-04-2025 500
O2 BLK-PUN-02 01-04-2025 300
O3 BLK-MUM-01 02-04-2025 700
O4 BLK-NSK-01 02-04-2025 200
O5 BLK-NGP-01 03-04-2025 100

DarkStore

Store ID City
BLK-PUN-01 Pune
BLK-PUN-02 Pune
BLK-MUM-01 Mumbai
BLK-NSK-01 Nashik

Exercise (one under the other):

  1. Enter both tables with Home › Enter data.
  2. Create a Date table: Date = CALENDAR(DATE(2025,4,1), DATE(2026,3,31)). Mark it as a date table.
  3. Relate DarkStore[Store ID] → Orders[Store ID] (one to many, single).
  4. Relate Date[Date] → Orders[Order Date] (one to many, single).
  5. Make Total Sales = SUM(Orders[Amount]).
  6. Build a matrix: Rows = DarkStore[City], Values = Total Sales.
  7. Fix the problem you find. Check the matrix again.

Expected result:

City Before the fix After adding BLK-NGP-01 → Nagpur
Pune ₹800 ₹800
Mumbai ₹700 ₹700
Nashik ₹200 ₹200
(Blank) ₹100 —
Nagpur — ₹100
Total ₹1,800 ₹1,800
Answer key (open after you try)
  • The (Blank) row means an Orders key has no match in DarkStore. Here it is BLK-NGP-01.
  • Fix the dimension: add the row BLK-NGP-01 | Nagpur to DarkStore (in the real job: in the source master file).
  • Do not hide (Blank) with a filter. That hides ₹100 of real sales.
  • The total does not change. Only the label moves from (Blank) to Nagpur.

Common mistakes:

What you see Why Fix
Relationship shows Many to many Store ID repeats in DarkStore Remove duplicates on DarkStore[Store ID] in Power Query
Every city shows ₹1,800 No relationship, or it is inactive Create it, or make it active
Month chart misses days Axis uses Orders[Order Date] Use Date[Date] / Date columns on axes
Cross filter set to Both "to be safe" Ambiguous paths, slow model Keep Single unless one measure needs CROSSFILTER

Samajla ka? A (Blank) row is not a Power BI bug. It is a message from your data.

Thodkyaat sangaycha tar (quick recap)

Use a star schema, keep relationships single direction, keep the Date table continuous, and do not forget Mark as date table. Got it? You will need these ideas in every module ahead. Now let us move on to hierarchies and groups.