Power BI grain, relationships and inactive relationships explained

The idea

The grain of a table is what one row means. In orders a row is one order. In order_items a row is one line of one order. In 'Date' a row is one calendar day. Before you build anything, you should be able to finish the sentence "one row of this table is ..." without an "or".

Grain looks like a vocabulary word, but it is the rule behind most modelling choices. A number belongs to the table whose grain it describes, and a relationship connects tables whose grains nest: many lines in one order, many orders on one day.

Figure 1

A value at order grain, copied onto order lines

orders: one row per order
order_id	shipping_fee
1003	4.99
1010	6.95

Flat copy: one row per order line, fee repeated
order_item_id	order_id	line_total	shipping_fee
8	1003	83.69	4.99
9	1003	314.10	4.99
23	1010	11.99	6.95
24	1010	26.99	6.95

Step 1 of 4 orders has one row per order, so its grain is the order. Each order has one shipping fee: $4.99 and $6.95, $11.94 in total.

orders has one row per order, so its grain is the order. Each order has one shipping fee: $4.99 and $6.95, $11.94 in total.
Copy the fee onto the order lines and the grain changes to one row per line. Order 1003 has two lines, so its fee is now written twice.
SUM(shipping_fee) on the copy returns $23.88 instead of $11.94. The data is not wrong. The sum is counting an order-level number once per line.
In the model, shipping_fee stays in orders and sums over one row per order. The relationship carries the filter, and no number is ever repeated.

Why it exists

A model that ignores grain gives wrong totals without giving any error. Power BI is happy to sum any column you give it. If a value that belongs to an order sits on every line of that order, the sum counts it once per line. Revenue is inflated, a report looks fine, and the manager decides on a number nobody checked.

Stating the grain also tells you which table a new column belongs in. The shipping fee describes the order, so it lives in orders. A line's quantity describes the line, so it lives in order_items. When a column fits two tables, you have not decided the grain of one of them yet.

How it works under the hood

Name the grain, then relate

Ask the grain question of every table in the model and write it down:

Table	One row is	Role
`order_items`	one product on one order	fact
`orders`	one order	header, relates the dimensions
`products`	one product	dimension
`customers`	one customer	dimension
`'Date'`	one calendar day	dimension

A relationship always joins a coarser grain (the 1 side) to a finer one (the many side). A relationship between two tables of the same grain is a one-to-one, which usually means they should be one table. Two tables that are each "many" to the other are many-to-many, and that is a sign that a dimension is missing between them.

What the engine checks

When you create a relationship in Model view (Home → Manage relationships → New), Power BI checks that the 1 side has no repeated key. A duplicate key on the supposed 1 side means the grain is not what you thought, and the relationship is refused or becomes many-to-many. Fix the grain (remove duplicates, or find the real key); do not change the cardinality to make the error go away.

One active path

Between any two tables, the model allows only one active path. Filters follow active relationships and nothing else. If two paths existed, a filter could reach a table by both routes and the answer would depend on which one the engine picked.

Figure 2

Two relationships from 'Date', one of them inactive

'Date'

Date
Year
Month

Year = 2025

orders

order_id
customer_id
order_date

customers

customer_id
signup_date
segment

order_items

order_item_id
order_id
line_total

Step 1 of 4 Sales are about the order date, so 'Date'[Date] relates to orders[order_date]. A Year slicer filters 'Date', and the filter flows to orders and order_items.

Sales are about the order date, so 'Date'[Date] relates to orders[order_date]. A Year slicer filters 'Date', and the filter flows to orders and order_items.
Marketing also wants signups by month, from customers[signup_date]. That is a second date, so a second relationship to the same calendar. It is drawn dashed: inactive.
If it were active, a Year filter would reach orders two ways: directly, and through customers. The two paths could give different sets of orders, so Power BI will not allow it.
An inactive relationship sleeps until a measure calls it: CALCULATE([Customers], USERELATIONSHIP(customers[signup_date], 'Date'[Date])) switches it on for that measure only.

This is why a second date has to be inactive. Harbor & Pine has two dates that mean different things: when an order was placed and when a customer signed up. Both relate to the one 'Date' table. The order date relationship is active because sales are the main story. The signup date relationship is inactive, drawn dashed, and a measure turns it on explicitly with USERELATIONSHIP. In the Edit relationship dialog the check box is called Make this relationship active.

The same calendar plays two roles, which is called a role-playing dimension. You keep one 'Date' table, with all its columns, and let different relationships use it. The alternative, a copy of the calendar for each date, doubles the maintenance and splits slicers.

Mark the calendar

One more rule belongs here. In a 'Date' table, use Table tools → Mark as date table and pick the Date column. That tells DAX which column is the calendar, and it unlocks time intelligence in the next chapters. The table must hold one row per day, with no gaps, from the first order date to the last.

Hierarchies sit on the grain too

A hierarchy is an ordered path through columns of one dimension, such as Year, Quarter, Month, Date. Create it in the Data pane (right-click a column, Create hierarchy) and a chart can drill from year to month with one click. It only works because each level nests inside the next: a month belongs to one year. A column that does not nest, like day of week, does not belong in that hierarchy. Month names also sort alphabetically unless you use Sort by column with the month number.

Common misconceptions

"Bidirectional filtering fixes any model problem"

A relationship set to Both lets filters climb from the many side to the one side as well. It looks like a cure when a slicer does not reach a table, and it often is the cause of the next bug: more paths, slower visuals, ambiguity. First ask whether the slicer is on the right table. The star usually needs single direction, from dimensions to facts.

"If a total looks plausible, the grain is right"

A repeated value does not break anything visibly. Every visual still draws a number; it is just too high. Nothing flags it. The check is to sum the column at its own grain, the $11.94 from orders here, and compare it with the same measure in a visual. If they differ, a value is being counted more than once.

"Two relationships between the same tables are a mistake"

They are fine as long as at most one is active. Role-playing a date is the most common reason to have two. Power BI itself creates the second one as inactive when it autodetects relationships. The mistake is two active paths, not two relationships.

Check yourself

One question. Answering it ticks this lesson off on the map; it never locks a level.

orders[shipping_fee] is one value per order. A colleague merges it onto the order lines so every line carries its order's fee, then sums the column. Order 1003 has two lines and a $4.99 fee. What goes wrong? The $4.99 is counted twice, because the sum now runs at the grain of a line, not an order Nothing, because DAX removes duplicate values before it sums The fee is dropped for order 1003, because a column can't belong to two tables The sum is correct, but the relationship must be changed to both directions