Most Power BI confusion comes down to one thing: not understanding how filters travel through your data model. This lesson breaks down exactly how relationships channel filter context from dimension tables to fact tables, why your measures behave the way they do, and how to take control with CALCULATE and USERELATIONSHIP.
Imagine you're building a sales report in Power BI. You have a table of orders, a table of products, and a table of customers. You drag a measure onto a visual, slice it by product category, and nothing happens — the number just sits there, stubbornly static, ignoring your slicer completely. Or worse, it changes, but gives you a number that makes no sense. You know the data is right. You know the measure looks right. But something invisible is breaking the connection between your filter and your result.
That invisible something is how relationships work in Power BI — and more specifically, how filters flow through those relationships to affect your DAX measures. Understanding this isn't optional. It's the foundational knowledge that separates people who copy DAX formulas from Stack Overflow and hope for the best from people who actually understand why a measure produces the number it does. Once you get this, debugging becomes logical, not mystical.
By the end of this lesson, you'll be able to build a multi-table data model, explain exactly how a filter applied in a visual travels through your tables, write measures that correctly aggregate data across related tables, and diagnose the most common relationship mistakes that cause silent errors.
What you'll learn:
You should be comfortable with the basic Power BI Desktop interface — you've opened the application, loaded some data, and created at least one simple measure using SUM or COUNT. You don't need deep DAX knowledge; we'll build from the ground up. Familiarity with the concept of tables and columns in Excel is enough of a foundation.
Before we talk about how filters flow, we need to be precise about what a relationship is in Power BI, because people often treat it as a visual connector on a diagram rather than understanding what it actually does at a computational level.
A relationship is a formal declaration that one column in one table shares values with one column in another table, and that this shared identity can be used to connect rows across tables. That's it. When you draw a relationship between Products[ProductID] and OrderDetails[ProductID], you are telling Power BI: "These two columns talk about the same things, and you can use that shared identity to connect a row in Products to the relevant rows in OrderDetails."
Think of it like a lookup. In your Orders table, you don't store the full product name, category, and price in every row — you just store a ProductID. The relationship lets Power BI look up everything it needs to know about that product in the Products table whenever it's evaluating data.
The most common type of relationship — and the one you should build almost everything around — is the one-to-many relationship. In this structure, one row in Table A corresponds to potentially many rows in Table B.
A classic example: one product can appear in many order lines. Your Products table has one row per product. Your OrderDetails table has one row per line item on an order, and many of those rows can reference the same product. That makes Products the "one" side and OrderDetails the "many" side.
In Power BI's Model view (click the third icon in the left sidebar, which looks like three connected shapes), you'll see this relationship drawn as a line between the two tables. The end of the line closest to the "one" table shows a 1, and the end closest to the "many" table shows an asterisk *.
The "one" side of a relationship is called the lookup table or dimension table. Products, Customers, Dates — these are dimension tables. The "many" side is called the fact table, because it contains the actual transactional facts: sales, clicks, orders, payments.
Key principle: In a well-designed model, your dimension tables should have unique values in the column you're using for the relationship. If Products has duplicate ProductIDs, Power BI can't create a proper one-to-many relationship — it'll try to make it many-to-many, which behaves very differently.
Now we can talk about what actually makes DAX measures work: filter context.
Every time you place a measure in a visual, Power BI doesn't just run your formula in a vacuum. First, it looks at the visual and asks: "What filters are currently active?" Those might come from slicers on the page, from row and column headers in a matrix, or from filters in the Filters pane. All of those active conditions together form the filter context — the set of rules defining which rows of your data are currently "visible" to the measure.
Here's a concrete example. Suppose you have this measure:
Total Sales = SUM(OrderDetails[LineTotal])
When this measure sits alone in a card visual with no filters, it sums every row in OrderDetails. The filter context is empty — all rows are visible. But when you put it in a matrix with Product Category on the rows, the visual creates a separate filter context for each category. For the "Electronics" row, Power BI filters the Products table to show only Electronics products, then your measure sums only the OrderDetails rows that belong to Electronics products.
That last part — "OrderDetails rows that belong to Electronics products" — is where relationships come in. The filter started on the Products table, and it traveled to the OrderDetails table through the relationship. That travel is called filter propagation.
This is the core mechanism, so let's be very deliberate about it.
When a filter is applied to a table, it automatically propagates to any related tables — but only in one specific direction by default: from the "one" side to the "many" side.
Think of it as a one-way valve. Dimension tables filter fact tables. Products filters OrderDetails. Customers filters Orders. Dates filters Sales. The arrow in Power BI's model diagram literally shows you which direction the filter flows. The arrowhead points toward the table that receives the filter.
Let's make this concrete with a three-table model:
Customers (1) -----> (*) Orders (1) -----> (*) OrderDetails
If you filter Customers to show only customers in New York, that filter propagates to Orders (so only New York customers' orders are visible), and then continues propagating to OrderDetails (so only line items on New York customers' orders are visible). A filter that starts at one end of this chain automatically flows all the way down.
This is why your measures work correctly even when your slicer is on a different table than the one being aggregated. The filter travels through the relationship chain to reach your fact table.
By default, filters do not flow from the many side to the one side. If you filter OrderDetails to show only line items where Quantity > 10, that filter does not automatically travel back to Products or Customers.
This is an intentional design choice. If you're looking at a large order with many items, you don't want that to accidentally affect your list of products or customers — the relationship is directional because the data semantics are directional. Products define order items, not the other way around.
This has a practical implication: measures that live on dimension tables don't automatically know about filters applied to the fact table, unless you explicitly write DAX to cross that boundary.
When you click on a relationship in the Model view, the Properties panel (or the Edit Relationship dialog, which you can reach by double-clicking the relationship line) shows you a Cross filter direction setting. It's either "Single" or "Both."
Single means filters flow in one direction only — from the one side to the many side. This is the default, and it's the right choice for the vast majority of relationships.
Both (bidirectional) means filters can travel in both directions simultaneously. A filter on the fact table propagates back to the dimension table, and vice versa.
Suppose you want a measure that counts "How many customers have placed at least one order?" You might think: I'll put a slicer on OrderDetails by product category, and I want my customer count measure to respond to it. With single-direction filtering, the slicer on products flows to orders and order details just fine — but a DISTINCTCOUNT(Customers[CustomerID]) measure might not work the way you expect unless the filter can propagate backwards.
This is one situation where bidirectional filtering feels appealing.
The problem with bidirectional filtering is that it creates ambiguous filter paths in more complex models. Imagine you have:
Products --> OrderDetails <-- Orders <-- Customers
If Products and Customers both have bidirectional relationships with their adjacent tables, a filter on Customers could ripple back through Orders, through OrderDetails, and somehow influence the Products list. Suddenly you're seeing only the products that those customers have ever ordered, not all products — and that might be completely wrong for your visual.
Bidirectional relationships also make it harder to reason about what your measures are doing, because the filter context becomes less predictable. Power BI can even throw an error if bidirectional relationships create a situation where a filter can travel in a loop.
Best practice: Keep all relationships as Single direction by default. If you think you need bidirectional, ask yourself whether you can achieve the same result with a DAX function like
CROSSFILTER()orUSERELATIONSHIP()— those give you explicit, controlled cross-filtering within a specific measure rather than model-wide.
Now let's write some actual DAX and see filter flow in action. We'll use a simple retail model with three tables:
The relationships are:
Total Revenue = SUMX(Sales, Sales[Quantity] * RELATED(Products[UnitPrice]))
Notice the RELATED() function. Because Products and Sales have a relationship, and we're iterating over Sales rows, RELATED(Products[UnitPrice]) travels from the current Sales row back to the related Products row to grab the unit price. This is filter flow working at the row level — the relationship makes cross-table lookups possible.
Now add a slicer on your report page that's connected to Products[Category]. When you select "Electronics," Power BI:
Your measure didn't change. The filter context changed, and the relationship channeled that context to the right rows.
Sometimes you want to compute a value that ignores part of the filter context, or modifies how filters travel. This is where CALCULATE() becomes essential.
All Products Revenue =
CALCULATE(
SUMX(Sales, Sales[Quantity] * RELATED(Products[UnitPrice])),
ALL(Products)
)
The ALL(Products) inside CALCULATE removes any filters on the Products table before evaluating the measure. So even if the user has selected "Electronics" in a slicer, this measure computes total revenue across all categories. This is how you build percentage-of-total calculations.
Category Revenue % =
DIVIDE(
[Total Revenue],
CALCULATE([Total Revenue], ALL(Products))
)
For the Electronics row in your visual, this divides Electronics revenue by all-products revenue. ALL(Products) is explicitly overriding the filter propagation for that specific calculation.
Power BI only allows one active relationship between any two tables. But sometimes you need two relationships. A classic example: a Sales table with both an OrderDate and a ShipDate, both of which should relate to a single Date dimension table.
You'd set up two relationships — one active (let's say on OrderDate), and one inactive (on ShipDate). In the Model view, active relationships appear as solid lines, inactive ones as dashed lines.
Inactive relationships don't participate in normal filter flow. But you can activate them for a specific measure using USERELATIONSHIP():
Revenue by Ship Date =
CALCULATE(
SUMX(Sales, Sales[Quantity] * RELATED(Products[UnitPrice])),
USERELATIONSHIP(Sales[ShipDate], 'Date'[Date])
)
This measure uses the inactive relationship just for this calculation, leaving all other measures unaffected. It's a surgical, explicit override of the default filter flow.
Build this yourself in Power BI Desktop to cement the concepts.
Step 1: Load the data Create three CSV files manually or in Excel:
Products.csv
ProductID,ProductName,Category,UnitPrice
1,Laptop,Electronics,999
2,Desk Chair,Furniture,349
3,Notebook,Stationery,4
4,Monitor,Electronics,349
Customers.csv
CustomerID,CustomerName,Region
1,Acme Corp,Northeast
2,Globex,Southwest
3,Initech,Northeast
Sales.csv
SaleID,CustomerID,ProductID,Quantity,SaleDate
1,1,1,2,2024-01-15
2,1,3,10,2024-01-20
3,2,2,1,2024-02-05
4,3,4,3,2024-02-10
5,2,1,1,2024-03-01
Load all three files using Home → Get Data → Text/CSV. Load each file separately.
Step 2: Build the relationships
In the Model view, confirm Power BI has detected the relationships automatically. If not, drag Products[ProductID] to Sales[ProductID], and Customers[CustomerID] to Sales[CustomerID]. Verify each shows a 1 on the Products/Customers end and a * on the Sales end.
Step 3: Create measures
Total Revenue = SUMX(Sales, Sales[Quantity] * RELATED(Products[UnitPrice]))
All Category Revenue = CALCULATE([Total Revenue], ALL(Products))
Revenue % of Total = DIVIDE([Total Revenue], [All Category Revenue])
Step 4: Build a matrix visual
Create a Matrix visual. Put Products[Category] on Rows and add all three measures as Values. Add a slicer on Customers[Region]. Select "Northeast" and watch how Total Revenue changes while All Category Revenue stays fixed. That's filter flow in action — and CALCULATE overriding it.
What to verify: When you filter by Region = Northeast, Total Revenue should drop (fewer customers), but All Category Revenue should stay the same because ALL(Products) removes the product filter but NOT the customer filter — only the filters on Products are removed, not filters on Customers that flow through Sales. Observe the behavior carefully. Does that match your intuition? If not, think through the filter chain again.
Mistake 1: Building relationships on non-unique columns
If your "one" side has duplicate values in the key column, Power BI will flag this as a many-to-many relationship, which behaves very differently and often produces inflated or incorrect totals. Fix this by ensuring dimension table keys are truly unique. Use DISTINCTCOUNT to check: if DISTINCTCOUNT(Products[ProductID]) equals COUNTROWS(Products), you're clean.
Mistake 2: Forgetting which direction filters flow A measure on the Products table counting products will not be affected by a filter on Sales unless you explicitly cross that boundary. If your measure seems to ignore a filter, check the relationship arrow direction in the Model view.
Mistake 3: Turning on bidirectional filtering to "fix" everything This feels like a quick fix but often introduces subtle calculation errors. Two measures that look correct independently can start interacting strangely when bidirectional filtering creates unintended filter paths. Always prefer explicit DAX functions over model-wide bidirectional settings.
Mistake 4: Using RELATED in the wrong direction
RELATED() only works when you're on the "many" side of a relationship looking up a value on the "one" side. If you're on the "one" side and want to aggregate values from the "many" side, use RELATEDTABLE() to get the set of related rows, then apply an aggregation.
-- On the Products table, count how many times each product was sold:
Times Sold = COUNTROWS(RELATEDTABLE(Sales))
Mistake 5: Multiple active relationships causing ambiguity
If you have two paths between the same two tables, Power BI might not know which path a filter should travel. You'll see an error in your relationship setup. Resolve this by making one relationship inactive and using USERELATIONSHIP() in measures where you need the alternate path.
Let's consolidate what you've learned. A relationship in Power BI is a formal connection between two columns that lets filters travel from one table to another. By default, filters flow from the "one" side (dimension tables) to the "many" side (fact tables) — never automatically in the opposite direction. This filter propagation is the mechanism that makes your slicers, row headers, and column headers affect your DAX measures.
You've seen that CALCULATE() is the tool for modifying or overriding that filter context within a specific measure, and that USERELATIONSHIP() lets you activate dormant relationships on demand. You've also learned that bidirectional relationships, while tempting, introduce unpredictability and should be used sparingly.
The most important mental model to carry forward: whenever a measure produces a surprising number, trace the filter context. Ask: What filters are active right now? Which tables do those filters sit on? Which direction do those tables' relationships point? Follow the chain, and the answer will almost always reveal itself.
What to learn next: