Home Course home Curriculum Learner dashboard Skill tree Business projects Datasets Final project
🧭 Module 0 · Data Analytics & Power BI Orientation Beginner LESSON 04 / 72

Datasets, Tables, Dimensions & Measures

25 min 5 quiz questions 1 practical challenge 4 syllabus topics

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.

Lesson 4 of 72 · Data Analytics & Power BI Orientation 6%
01

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.

By the end of this lesson you will be able to
  • 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
02

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.
Why these are dangerous rather than merely wrong
None of them throws an error. None of them looks broken. They produce plausible numbers that are wrong, which flow into decisions. This is the specific failure mode that makes data vocabulary worth twenty-five minutes of your attention.
03

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.

Grain examples from the course datasets
TableOne row is…Implication
bwb-retail-sales.csvone product line within one orderCounting rows gives lines, not orders. There are 15,067 rows but only 7,607 orders.
bwb-customers.csvone customerSafe to count rows for a customer total. This is a proper dimension.
bwb-products.csvone productSafe to count. Price and cost here are attributes, not things to sum.
bwb-hr-employees.csvone employee, ever (including leavers)Current headcount needs a filter; total rows is not headcount.
bwb-healthcare-encounters.csvone hospital encounterPatients appear more than once, so encounters ≠ patients.
bwb-finance-actuals-budget.csvone account, in one cost centre, in one monthSumming across all three dimensions is valid; summing a percentage across them is not.
The habit to build now
Every single time you load a table, write its grain down in one sentence before you do anything else. "One row is one order line." It takes five seconds and it prevents the most expensive class of analytical error.

Dimensions and measures

This is the split that organises everything:

Dimensions vs measures
DimensionMeasure
What it isSomething you slice, group or filter bySomething you aggregate — a number you total, average or count
Question it answers"By what?""How much?" / "How many?"
ExamplesCategory, Region, Customer, Date, Channel, DepartmentRevenue, Profit, Quantity, Order count, Headcount
Data typeUsually text or dateAlmost always numeric
Lives inDimension tablesFact tables (as columns), and DAX measures over them
CardinalityRelatively few distinct valuesMany distinct values, often continuous
Numeric does not mean measure
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.50 on an order line is a fact. That sale happened.
  • UnitPrice = 89.99 in 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.

The three kinds of measure
TypeDefinitionExamplesDanger
AdditiveSums correctly across every dimension, including timeRevenue, Quantity, Profit, COGSNone — these are the easy ones
Semi-additiveSums across some dimensions but not across timeInventory level, account balance, headcount, open ticketsSumming across months gives a wildly inflated number
Non-additiveNever sums; must be recalculated at every levelAny 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.

04

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.

Grain in practice — a three-line order
OrderIDProductKeyQuantityNetSales
ORD-100001P-10152249.98
ORD-100001P-1042189.99
ORD-100001P-1103344.97
ORD-100002P-10151124.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?

The semi-additive trap, concretely
Warehouse stock: 40,000 units in January, 41,000 in February, 39,000 in March. Sum across the quarter = 120,000 units. But you never had 120,000 units — you had roughly 40,000 the whole time. For a snapshot measure, the correct quarterly figure is usually the last value, not the sum. Module 6 covers the DAX for this.
05

Power BI Demonstration

Power BI shows you a lot of this vocabulary directly in the interface, once you know what to look for.

Where the vocabulary appears in Desktop
What you seeWhat it tells you
A field with a Σ (sigma) icon in the Data panePower 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 iconA DAX measure. It is calculated at query time, in filter context — this is what you want for any ratio.
A field with a calendar iconA date column. If it also has a hierarchy under it, auto date/time is on.
A field in italicsA calculated column or calculated table, added with DAX rather than loaded.
The Summarization setting in Column toolsSet 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.
Data viewselect a columnColumn toolsSummarizationDon't summarize
Find the numeric columns that are secretly dimensions
In Data view, look at every column with a Σ. For each, ask: does summing this mean anything? Year, SeverityScore, StoreKey if numeric — all no.
Set their summarization to Don't summarize
Select the column, then Column tools › Summarization › Don't summarize. Now dragging it into a visual groups by it instead of summing it, which is what you meant.
Hide the raw numeric columns you will only use through measures
Once you have written a 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.
Write ratios as measures, never as columns
A margin column calculated per row will average incorrectly in every total. A margin measure dividing two summed measures is correct at every level, automatically.
Hiding raw numeric columns is a professional signal
In a well-built model, the report author sees measures, not raw fact columns. It makes the model self-documenting and it makes implicit-aggregation mistakes structurally impossible. Reviewers notice this immediately.
06

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.

07

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?

  1. Category in the products table
  2. NetSales in the sales table
  3. Discount (a rate, 0 to 0.30) in the sales table
  4. Year in a date table
  5. ClosingStockUnits in the supply chain table
  6. AnnualSalary in the HR table
  7. SatisfactionScore (1–5) in the HR table
  8. LengthOfStayDays in the healthcare table
  9. Profit margin percentage
  10. 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.

08

Expected Result

Classification answers
ItemTypeNotes
CategoryDimensionText, few distinct values, you slice by it
NetSalesMeasure — additiveSums correctly across products, regions, time, everything
DiscountNeither, exactlyA 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
YearDimensionNumeric but you group by it. Set Summarization to Don't summarize
ClosingStockUnitsMeasure — semi-additiveSums across warehouses and products, but not across time. It is a snapshot
AnnualSalaryMeasure — semi-additiveSumming across employees gives total payroll cost (valid). Summing the same employee across months would not be
SatisfactionScoreBoth, depending on useAverage it as a measure, or band it (1–2 / 3 / 4–5) and slice by it. Never sum it
LengthOfStayDaysMeasure — additiveSumming gives total bed days, which is a genuinely useful capacity measure
Profit margin %Measure — non-additiveMust be DIVIDE(SUM(Profit), SUM(NetSales)), recalculated at every level
Current headcountMeasure — semi-additiveSums across departments and regions, but not across time — you do not add January and February headcount
The sales table's grain, and its limits
Grain: one row is one product line within one order.

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.
09

Common Mistakes

Counting rows to count events
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.
Averaging a ratio
Creating a per-row margin % column and then averaging it. Small rows get the same weight as large ones, so the answer is dominated by whichever rows happen to be numerous.

Fix: Always DIVIDE(SUM(Profit), SUM(NetSales)). The measure recalculates correctly at every level of every visual, automatically, forever.
Summing a snapshot across time
Total inventory across twelve months, or total headcount across four quarters. The number is meaningless but nothing warns you.

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.
Letting Power BI sum a numeric dimension
Dragging 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.
Treating a dimension attribute as a fact
Summing 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.
10

Professional Tip

The four-question table intake ritual
Every time a new table enters your model, answer these four questions in writing before you do anything else. It takes two minutes and it prevents most of what goes wrong later.

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, not Sum 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.
11

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?

12

Practical Challenge

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.

Requirements
  • 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:

  1. Does this describe something (an attribute), or does it measure something that happened (a fact)?
  2. 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.

13

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

One sentence
One row is one purchase order, placed on one date, with one supplier, for one product, delivered to one warehouse.

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

ColumnTypeAdditivity / notes
PurchaseOrderIDKeyUnique identifier
OrderDate, PromisedDate, ReceivedDateDimension (date)Three separate dates — a role-playing dimension situation, exactly like OrderDate/ShipDate in the sales table
SupplierKey, SupplierName, SupplierCountryDimension attributesDenormalised supplier data — these belong in a supplier dimension table
WarehouseKey, WarehouseName, WarehouseRegionDimension attributesDenormalised warehouse data — same treatment
ProductKey, ProductCategoryDimension attributesJoin to the product dimension
QuantityOrderedFactAdditive
QuantityReceivedFactAdditive
PurchaseCostFactAdditive
PromisedLeadDaysAttribute, not a factNon-additive — see below
ActualLeadDaysFact (a duration)Non-additive in practice — average it, do not sum it
DelayDaysFact (a duration)Non-additive — average it. Note negatives are meaningful
DeliveryStatusDimensionText category — slice by it
ClosingStockUnitsFactSemi-additive — a snapshot, does not sum across time
SupplierQualityScoreAttribute, not a factNon-additive — see below

Two columns that look like facts but are not

  1. 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.
  2. 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

Fill Rate %
In words: total quantity received divided by total quantity ordered, across whatever the visual is currently filtered to.

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.
What a reviewer would notice about this answer
Three things: that you spotted the line-versus-order grain trap without being told; that you identified the two attribute columns hiding in the fact table, which is a genuine modelling insight rather than a lookup; and that you flagged negative DelayDays as meaningful rather than as dirty data. All three are the kinds of observation that separate someone who has understood the concepts from someone who has memorised definitions.
14

Lesson Summary

Key takeaways
  • 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 — Year is 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

Understanding datasets and tablesDimensions vs measuresGranularityAdditive vs non-additive measures
15

Progress

Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.

Lesson complete — 6% of Power BI Mastery done
Progress saved. Keep the momentum: the next lesson builds directly on this one.