11.2 Fact Tables and Dimension Tables in Power BI with Example
Quick answer: the short, self-contained version of this lesson is the guide Fact and Dimension Tables in Power BI.
Start with a problem
Shraddha's flat orders sheet repeats City, Region and Manager on every row. Someone typed Nasik once, and Nashik split into two bars.
Explain simply
A fact table stores events and numbers (one row per order). A dimension table stores descriptions you filter by (one row per store). A relationship joins them on a key.
Show the data
Flat sheet (first 4 of 8 orders):
| Order ID | Store | City | Region | Manager | Amount (₹) |
|---|---|---|---|---|---|
| 301 | Kothrud | Pune | West Maharashtra | Ravindra | 400 |
| 302 | Baner | Pune | West Maharashtra | Ravindra | 200 |
| 303 | Gangapur | Nashik | North Maharashtra | Shraddha | 300 |
| 304 | College Road | Nashik | North Maharashtra | Shraddha | 150 |
About this data: This table uses columns: Order ID, Store, City, Region, Manager, Amount (₹). This table uses columns: Store, City, Region, Manager. This table uses columns: Region, City, Amount (₹). This table uses columns: Order ID, Store ID, City, Manager, Amount (₹). Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: visuals_orders_apr_may_2026.csv, dark_stores_maharashtra.csv (source: src/practice/).
Demonstrate
- Load
visuals_orders_apr_may_2026.csvas Orders anddark_stores_maharashtra.csvas DarkStore. -
In Orders, remove City (it now lives in DarkStore).
Your result should look like this:
Orders columns Order ID, Order Date, Store, Platform, Category, Amount, Delivery Mins, Status -
In DarkStore, keep Store, City, Region, Manager.
Your result should look like this:
Store City Region Manager Kothrud Pune West Maharashtra Ravindra Baner Pune West Maharashtra Ravindra Gangapur Nashik North Maharashtra Shraddha College Road Nashik North Maharashtra Shraddha Rajarampuri Kolhapur South Maharashtra Zoya -
Model view: drag DarkStore[Store] onto Orders[Store] › One-to-many (1 → *), single direction › OK.
- Matrix: DarkStore[Region] and DarkStore[City] on Rows; Sum of Amount on Values.
Show the result
Your result should look like this (fictional sample):
| Region | City | Amount (₹) |
|---|---|---|
| West Maharashtra | Pune | 850 |
| North Maharashtra | Nashik | 800 |
| South Maharashtra | Kolhapur | 450 |
| Total | 2,100 |
Explain what happened
Orders is the fact table: 8 events with numbers. DarkStore is the dimension: 5 stores with descriptions, each written once. The relationship sends the City filter from DarkStore to Orders.
Explore further
- Say the grain out loud: "one row per order". Most modelling bugs start with an unclear grain.
- Facts are long and narrow; dimensions are short and wide.
Detailed reference for this lesson:
Fact table and dimension table in Power BI with example — in 30 seconds
What: A fact table stores events and numbers. A dimension table stores descriptions you filter by.
Why: If City and Manager repeat on every order row, the file is big and one spelling mistake splits a city.
Tiny example: One flat table becomes Orders (numbers) + DarkStore (City, Manager).
Fact table and dimension table in Power BI with example — 30 सेकंदात
काय: Fact table मध्ये घटना आणि आकडे असतात. Dimension table मध्ये ज्यावर filter करायचं ती वर्णनं असतात.
का: प्रत्येक order row वर City आणि Manager repeat होत असतील तर file मोठी होते आणि एका spelling चुकीने एक शहर दोन तुकड्यांत फुटतं.
छोटं उदाहरण: एका flat table चे Orders (आकडे) + DarkStore (City, Manager) असे दोन tables होतात.
Fact table and dimension table in Power BI with example — 30 सेकंड में
क्या: Fact table में घटनाएँ और आँकड़े होते हैं. Dimension table में वे विवरण होते हैं जिन पर filter करते हैं.
क्यों: हर order row पर City और Manager दोहराए जाएँ तो file बड़ी होती है और एक spelling की ग़लती से एक शहर दो हिस्सों में बँट जाता है.
छोटा उदाहरण: एक flat table से Orders (आँकड़े) + DarkStore (City, Manager) दो tables बनती हैं.
First look at the data (before → after):
Same before / after tables as text
Before — One flat table (City and Manager repeat)
| Order ID | Store ID | City | Manager | Amount (₹) |
|---|---|---|---|---|
| 101 | BLK-PUN-01 | Pune | Ravindra | 500 |
| 102 | BLK-NSK-01 | Nashik | Shraddha | 300 |
| 103 | BLK-PUN-01 | Pune | Ravindra | 700 |
Formula / click path
Power Query: Reference the flat query twice
Orders keeps keys + numbers · DarkStore keeps unique Store ID + text
After — Orders (fact)
| Order ID | Store ID | Amount (₹) |
|---|---|---|
| 101 | BLK-PUN-01 | 500 |
| 102 | BLK-NSK-01 | 300 |
| 103 | BLK-PUN-01 | 700 |
After — DarkStore (dimension)
| Store ID | City | Manager |
|---|---|---|
| BLK-PUN-01 | Pune | Ravindra |
| BLK-NSK-01 | Nashik | Shraddha |
Do it step by step:
- In Power Query, right-click the flat query › Reference. Name it
DarkStore. - In DarkStore keep only Store ID, City, Manager.
- Select Store ID › Remove Rows › Remove Duplicates.
- Reference the flat query again. Name it
Orders. Keep Order ID, Store ID, Amount. - Close & Apply. In Model view, drag DarkStore[Store ID] onto Orders[Store ID].
एक-एक step करून बघा:
- Power Query मध्ये flat query वर right-click करा › Reference. नाव
DarkStoreद्या. - DarkStore मध्ये फक्त Store ID, City, Manager ठेवा.
- Store ID निवडा › Remove Rows › Remove Duplicates.
- Flat query पुन्हा Reference करा. नाव
Ordersद्या. Order ID, Store ID, Amount ठेवा. - Close & Apply. Model view मध्ये DarkStore[Store ID] ओढून Orders[Store ID] वर सोडा.
एक-एक step करके देखिए:
- Power Query में flat query पर right-click कीजिए › Reference. नाम
DarkStoreरखिए. - DarkStore में सिर्फ़ Store ID, City, Manager रखिए.
- Store ID चुनिए › Remove Rows › Remove Duplicates.
- Flat query को फिर Reference कीजिए. नाम
Ordersरखिए. Order ID, Store ID, Amount रखिए. - Close & Apply. Model view में DarkStore[Store ID] खींचकर Orders[Store ID] पर छोड़िए.
| Fact table | Dimension table | |
|---|---|---|
| Contains | Events/transactions and numeric values (measures) | Descriptive attributes used to filter and group |
| Examples | Orders, Inventory snapshots, App sessions, Deliveries | Product, Customer, Date, DarkStore, DeliveryPartner |
| Rows | Many (thousands to billions) | Relatively few |
| Columns | Keys (foreign keys) + numbers | A unique key + descriptive text columns |
| Typical question | "How much?" "How many?" | "By what?" "Who?" "When?" "Where?" |
| In our sample | Orders |
Product, Customer, DarkStore, DeliveryPartner, Date |
Keys: a dimension has a primary key (the column whose value identifies each row uniquely) – a column with unique values (e.g. Product[Product ID]). The fact table holds that value as a foreign key (the column that points to the matching row in another table) (Orders[Product ID]). Some models use a surrogate key – an artificial integer key created in the data warehouse.
Ravindra Bagale's Tip
A common mistake is putting descriptive text (Product Name, City) in the fact table and numbers in dimensions. Facts hold events and numbers (Orders: Amount, Quantity); dimensions hold descriptions (Product, DarkStore, Customer, Date). Ask "is this something I count or sum, or something I filter by?". Keep this in mind!
Ravindra Bagale's Tip – मराठी
एक common चूक म्हणजे वर्णन करणारा text (Product Name, City) fact table मध्ये आणि numbers dimensions मध्ये ठेवणं. Facts मध्ये events आणि numbers असतात (Orders: Amount, Quantity); dimensions मध्ये वर्णन असतं (Product, DarkStore, Customer, Date). स्वतःला विचारा, "हे मी मोजतो किंवा बेरीज करतो, की याने filter करतो?". लक्षात ठेवा!
Ravindra Bagale's Tip – हिंदी
एक common गलती है विवरण वाला text (Product Name, City) fact table में और numbers dimensions में रखना. Facts में events और numbers होते हैं (Orders: Amount, Quantity); dimensions में विवरण होता है (Product, DarkStore, Customer, Date). ख़ुद से पूछो, "क्या इसे मैं गिनता या जोड़ता हूँ, या इससे filter करता हूँ?". ध्यान रखना!
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Nashik appears twice | City text typed on every fact row | Keep City only in the dimension |
| Every region shows ₹2,100 | No relationship | Relate DarkStore[Store] to Orders[Store] |
| The relationship becomes many-to-many | Store is not unique in DarkStore | Remove duplicates in the dimension |
Practise independently
Practice task
Using the model, what is the amount and order count by Manager?
Hints
- Ravindra: Kothrud + Baner; Shraddha: Gangapur + College Road; Zoya: Rajarampuri.
Solution
Ravindra ₹850 (3 orders), Shraddha ₹800 (3 orders), Zoya ₹450 (2 orders, one cancelled).
Think like a professional
Before building visuals, write the grain and list facts versus dimensions. Many "wrong total" bugs start in a model that skipped this step.
Revision card
Comparison card