Datasets, Tables, Dimensions & Measures
This lesson is pure vocabulary, and it is worth doing carefully. Almost every confusing thing in Power BI later — why a relationship goes one way, why a number does not add up, why a measure behaves oddly in a total — traces back to one of the four ideas here: grain, dimensions, measures, and additivity.
Learning Objective
Get these four ideas right and Modules 4, 5 and 6 become much easier. Get them wrong and you will spend those modules confused about why your numbers misbehave.
- Describe what a dataset, a table, a row and a column are in Power BI terms
- State the grain of any table you are given, and explain why grain matters
- Distinguish a dimension from a measure, and an attribute from a fact
- Explain additive, semi-additive and non-additive measures with examples
- Predict which numbers will misbehave in a total before you build anything
Why This Matters
Three real failures, all caused by getting the vocabulary wrong:
- An analyst counts rows in a sales table to report "orders" — but the table's grain is one row per order line. Every order-count and average-order-value figure is roughly double. Nobody notices for a quarter.
- Someone builds an "average margin %" measure by averaging a per-row margin column. The total is nonsense, because you cannot average a ratio. The card shows a plausible number, which is the worst possible outcome.
- A report sums the inventory level across twelve months and reports 480,000 units in stock. The real figure is 40,000 — stock is a snapshot, and snapshots do not add across time.
Concept
Datasets, tables, rows and columns
In Power BI, a semantic model (formerly called a dataset) is the whole package: the tables, their relationships, and the DAX measures written over them. It is what a report queries.
Inside it, a table is a rectangular grid. Each row is one instance of something. Each column is one attribute, and every value in a column shares a single data type — Power BI does not permit mixed types in a column, which is a feature, not a limitation.
Grain — the most important idea in this lesson
The grain (or granularity) of a table is the answer to: what does one row represent? Say it as a sentence. If you cannot, you do not yet understand the table.
| Table | One row is… | Implication |
|---|---|---|
bwb-retail-sales.csv | one product line within one order | Counting rows gives lines, not orders. There are 15,067 rows but only 7,607 orders. |
bwb-customers.csv | one customer | Safe to count rows for a customer total. This is a proper dimension. |
bwb-products.csv | one product | Safe to count. Price and cost here are attributes, not things to sum. |
bwb-hr-employees.csv | one employee, ever (including leavers) | Current headcount needs a filter; total rows is not headcount. |
bwb-healthcare-encounters.csv | one hospital encounter | Patients appear more than once, so encounters ≠ patients. |
bwb-finance-actuals-budget.csv | one account, in one cost centre, in one month | Summing across all three dimensions is valid; summing a percentage across them is not. |
Dimensions and measures
This is the split that organises everything:
| Dimension | Measure | |
|---|---|---|
| What it is | Something you slice, group or filter by | Something you aggregate — a number you total, average or count |
| Question it answers | "By what?" | "How much?" / "How many?" |
| Examples | Category, Region, Customer, Date, Channel, Department | Revenue, Profit, Quantity, Order count, Headcount |
| Data type | Usually text or date | Almost always numeric |
| Lives in | Dimension tables | Fact tables (as columns), and DAX measures over them |
| Cardinality | Relatively few distinct values | Many distinct values, often continuous |
Year is a number but it is a dimension — you slice by it. A postcode may be numeric but you would never sum it. UnitPrice in a product table is numeric but it is an attribute of the product, not something to total across products. The test is not the data type; it is does summing this produce something meaningful?Facts and attributes
Closely related: a fact is a measurement of an event — it happened, at a point in time, and it produced a number. An attribute describes a thing rather than measuring an event.
NetSales = 1249.50on an order line is a fact. That sale happened.UnitPrice = 89.99in the product table is an attribute. It describes the product's current price; nothing happened.- Sum the first across a period and you get revenue. Sum the second across products and you get a meaningless number.
Additive, semi-additive and non-additive
This determines whether a measure behaves correctly in totals and subtotals — and it is where most "my total doesn't match" problems come from.
| Type | Definition | Examples | Danger |
|---|---|---|---|
| Additive | Sums correctly across every dimension, including time | Revenue, Quantity, Profit, COGS | None — these are the easy ones |
| Semi-additive | Sums across some dimensions but not across time | Inventory level, account balance, headcount, open tickets | Summing across months gives a wildly inflated number |
| Non-additive | Never sums; must be recalculated at every level | Any ratio: margin %, conversion rate, average price, on-time % | Averaging a per-row ratio produces a plausible wrong answer |
Why ratios must be recalculated
Two products. Product A: revenue $100, profit $50 → 50% margin. Product B: revenue $900, profit $90 → 10% margin.
- Wrong: average the margins → (50 + 10) ÷ 2 = 30%
- Right: recalculate at the total → (50 + 90) ÷ (100 + 900) = 14%
The difference is enormous, and the wrong answer looks perfectly reasonable. The rule: always write a ratio as a measure that divides two summed measures, never as an average of a calculated column. Module 5 Lesson 2 shows exactly how.
Visual Explanation
The same data, shown at two different grains. Notice how the row count changes but the revenue total does not — and how the order count only becomes correct at the right grain.
| OrderID | ProductKey | Quantity | NetSales |
|---|---|---|---|
| ORD-100001 | P-1015 | 2 | 249.98 |
| ORD-100001 | P-1042 | 1 | 89.99 |
| ORD-100001 | P-1103 | 3 | 44.97 |
| ORD-100002 | P-1015 | 1 | 124.99 |
- Row count = 4. That is order lines.
- DISTINCTCOUNT of OrderID = 2. That is orders.
- SUM of NetSales = 509.93. Correct at either grain — revenue is additive.
- Average order value. 509.93 ÷ 2 = $254.97 (right). 509.93 ÷ 4 = $127.48 (wrong, and it looks fine).
Which of these numbers is safe to add?
Power BI Demonstration
Power BI shows you a lot of this vocabulary directly in the interface, once you know what to look for.
| What you see | What it tells you |
|---|---|
| A field with a Σ (sigma) icon in the Data pane | Power BI has inferred this numeric column is summable, and will default to Sum when you drag it into a visual. It is often right, and sometimes badly wrong. |
| A field with a calculator icon | A DAX measure. It is calculated at query time, in filter context — this is what you want for any ratio. |
| A field with a calendar icon | A date column. If it also has a hierarchy under it, auto date/time is on. |
| A field in italics | A calculated column or calculated table, added with DAX rather than loaded. |
| The Summarization setting in Column tools | Set this to Don't summarize for any numeric column that is really a dimension — Year, postcode, ID numbers. This is the fix for the Σ appearing on a column that should not be summed. |
Year, SeverityScore, StoreKey if numeric — all no.Total Revenue measure, hide the raw NetSales column from report view. This stops anyone — including you in six months — dragging the raw column in and getting an implicit sum that bypasses your definition.Example Dataset
This lesson is conceptual and needs no download — it uses the sample data shown inline above. The next lesson that requires a file will link it here.
You can still browse every dataset in the course from the dataset library.
Step-by-Step Exercise
Classification drill. For each item, decide: dimension or measure? And if a measure, is it additive, semi-additive or non-additive?
Categoryin the products tableNetSalesin the sales tableDiscount(a rate, 0 to 0.30) in the sales tableYearin a date tableClosingStockUnitsin the supply chain tableAnnualSalaryin the HR tableSatisfactionScore(1–5) in the HR tableLengthOfStayDaysin the healthcare table- Profit margin percentage
- Current headcount
Then, for the whole sales table, write its grain in one sentence and list two questions you could not answer at that grain.
Expected Result
| Item | Type | Notes |
|---|---|---|
| Category | Dimension | Text, few distinct values, you slice by it |
| NetSales | Measure — additive | Sums correctly across products, regions, time, everything |
| Discount | Neither, exactly | A rate. You can slice by it if you band it, but never sum it. To report an average discount, weight it by revenue — a non-additive calculation |
| Year | Dimension | Numeric but you group by it. Set Summarization to Don't summarize |
| ClosingStockUnits | Measure — semi-additive | Sums across warehouses and products, but not across time. It is a snapshot |
| AnnualSalary | Measure — semi-additive | Summing across employees gives total payroll cost (valid). Summing the same employee across months would not be |
| SatisfactionScore | Both, depending on use | Average it as a measure, or band it (1–2 / 3 / 4–5) and slice by it. Never sum it |
| LengthOfStayDays | Measure — additive | Summing gives total bed days, which is a genuinely useful capacity measure |
| Profit margin % | Measure — non-additive | Must be DIVIDE(SUM(Profit), SUM(NetSales)), recalculated at every level |
| Current headcount | Measure — semi-additive | Sums across departments and regions, but not across time — you do not add January and February headcount |
Two questions you cannot answer at this grain:
• How many items did a customer view before buying? — browsing events are not in this table at all. No grain change helps; the data does not exist.
• What was the stock level when the order was placed? — that is a different fact table at a different grain (product × date), and it would need to be joined in.
Recognising that a question needs data you do not have — rather than trying to torture an answer out of what you do have — is a genuine professional skill.
Common Mistakes
COUNTROWS(Sales) on a line-level fact table gives you lines, not orders. Every order count and every average-order-value figure derived from it is wrong, and looks completely plausible.Fix: State the grain before you count anything. Use
DISTINCTCOUNT(Sales[OrderID]) for orders. If the two numbers differ, you have just learned something important about your table.Fix: Always
DIVIDE(SUM(Profit), SUM(NetSales)). The measure recalculates correctly at every level of every visual, automatically, forever.Fix: Identify semi-additive measures up front and decide the correct time behaviour — usually last value, sometimes average. Then write the measure to do that explicitly.
Year into a visual and getting 4,050 because it summed 2024 and 2025 — twice.Fix: Set Summarization to Don't summarize on every numeric column that is really a dimension. Do this once, at model-build time, not every time you use the field.
UnitPrice from the product table to get a "total price". It is an attribute describing each product, not a measurement of anything that happened.Fix: Ask whether the number records an event or describes a thing. Only event measurements belong in a fact table and only they should be summed.
Professional Tip
1. What does one row represent? (the grain, as a sentence)
2. What uniquely identifies a row? (the key — and check it is actually unique)
3. Which columns are dimensions and which are facts?
4. For each fact, is it additive, semi-additive or non-additive?
Experienced modellers do this without noticing. Doing it explicitly until it becomes automatic is the fastest route to that point.
A related discipline that will serve you well:
- Name measures for what they mean, not how they are computed.
Total Revenue, notSum of NetSales. The first survives a change in how it is calculated; the second becomes a lie the moment you refine it. - Put the unit in the name when there is any doubt.
Avg Length of Stay (days)removes an entire category of misunderstanding at zero cost.
Knowledge Check
Knowledge Check
5 questions. Answer each one to see why the right answer is right.
A sales table has 15,067 rows and 7,607 distinct OrderIDs. A colleague reports "7,607 orders at an average value of $419". Total revenue is $6,311,491. What is wrong?
Which of these is a semi-additive measure?
Product A: $100 revenue, $50 profit. Product B: $900 revenue, $90 profit. What is the correct combined margin?
You drag a Year column into a card visual and it shows 4,049. What happened, and what is the fix?
What is the single most useful thing to write down when a new table enters your model?
Practical Challenge
Produce a data dictionary for an unfamiliar table
Open the documentation for bwb-supply-chain.csv in the dataset library — 2,200 rows, 21 columns of purchase order data you have not worked with. Produce a data dictionary for it, without loading it into Power BI.
- State the grain in one sentence
- Identify the candidate key
- Classify every column as key, dimension attribute, or fact
- For each fact, state whether it is additive, semi-additive or non-additive
- Name two columns that look like facts but are not, and explain why
- Name one measure you would have to compute rather than sum, and give its formula in words
Work through the column list and ask two questions about each one:
- Does this describe something (an attribute), or does it measure something that happened (a fact)?
- If it is a fact: would summing it across suppliers, warehouses and months produce a meaningful number?
Pay particular attention to ClosingStockUnits, SupplierQualityScore, PromisedLeadDays and DelayDays. Each of those is interesting for a different reason.
Solution
Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.
A model data dictionary
Grain
Note what this implies: a PO for three different products would appear as three rows. So COUNTROWS gives PO lines, and DISTINCTCOUNT(PurchaseOrderID) gives orders — the same trap as the sales table.
Candidate key
PurchaseOrderID is the intended key. Given the grain above, you should verify it is actually unique before trusting it — in this dataset it is, but checking rather than assuming is the habit worth building.
Column classification
| Column | Type | Additivity / notes |
|---|---|---|
| PurchaseOrderID | Key | Unique identifier |
| OrderDate, PromisedDate, ReceivedDate | Dimension (date) | Three separate dates — a role-playing dimension situation, exactly like OrderDate/ShipDate in the sales table |
| SupplierKey, SupplierName, SupplierCountry | Dimension attributes | Denormalised supplier data — these belong in a supplier dimension table |
| WarehouseKey, WarehouseName, WarehouseRegion | Dimension attributes | Denormalised warehouse data — same treatment |
| ProductKey, ProductCategory | Dimension attributes | Join to the product dimension |
| QuantityOrdered | Fact | Additive |
| QuantityReceived | Fact | Additive |
| PurchaseCost | Fact | Additive |
| PromisedLeadDays | Attribute, not a fact | Non-additive — see below |
| ActualLeadDays | Fact (a duration) | Non-additive in practice — average it, do not sum it |
| DelayDays | Fact (a duration) | Non-additive — average it. Note negatives are meaningful |
| DeliveryStatus | Dimension | Text category — slice by it |
| ClosingStockUnits | Fact | Semi-additive — a snapshot, does not sum across time |
| SupplierQualityScore | Attribute, not a fact | Non-additive — see below |
Two columns that look like facts but are not
SupplierQualityScore— it is numeric and sits in the fact table, so it looks like a measurement. It is not: it is an attribute of the supplier, repeated on every row for that supplier. Summing it multiplies one supplier's score by their order count, which is meaningless. Averaging it across rows is also subtly wrong — it weights by order volume rather than giving each supplier equal weight. The correct home for this column is a supplier dimension table with one row per supplier.PromisedLeadDays— same problem. It is the supplier's committed lead time, an attribute of the supplier–product arrangement, not a measurement of this specific order. It is useful as a comparison baseline, not as something to total.
A measure that must be computed, not summed
Why it cannot be summed or averaged: a per-row fill ratio averaged across orders gives a 50-unit order the same weight as a 1,000-unit order. The correct figure divides two additive totals, so it automatically reweights at every level — by supplier, by warehouse, by month, by category.
This is the single most common error in supply chain reporting, and it is the same non-additive ratio rule from section 03 wearing different clothes.
Lesson Summary
- A semantic model is tables plus relationships plus measures — the thing a report queries.
- Grain is what one row represents. State it as a sentence for every table, every time. Almost every counting error traces back to not doing this.
- Dimensions are what you slice by; measures are what you aggregate. Numeric does not mean measure —
Yearis a dimension. - Additive measures sum everywhere (revenue, quantity). Semi-additive ones do not sum across time (stock, headcount). Non-additive ones never sum (any ratio).
- Never average a ratio. Write it as one summed measure divided by another, and it will be correct at every level automatically.
- Set Summarization to Don't summarize on numeric dimensions, and hide raw fact columns once measures exist.
- Run the four-question intake ritual on every new table: grain, key, dimensions vs facts, additivity.
Syllabus topics delivered
Progress
Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.