Understanding Power BI Relationships & Filter Direction

[ajaybabuinturi]

When you start building a report on Power BI platform, you can quickly see lines connecting tables in the Model View. It looks very simple but behind those lines lies the secret of how Power BI understands your data. What do these actually mean?
Those lines are representing Relationships, one of the most important parts of building a good Power BI data model. Relationships define how data from different tables connects and communicates. In simple terms, they tell Power BI how your tables “know” each other.

Example:

Let’s explore Power BI relationships with a simple, real-world story: Imagine you manage a small Bike store throughout the country. You have two tables, one with Customer information and another with their Orders details.

Customers Table		Orders Table
CustomerID		OrderID
Name		CustomerID
City		Product
Age		Amount

Each customer can place multiple orders. So, one customer places many orders i.e customers (1) --> orders (*). 

This is the most common relationship type in Power BI: one-to-many (1:*).

What Are Relationships in Power BI?

Relationships are defining how data in one table connects to another table. Without relationships, Power BI has no idea how to connect customers to their orders or products to their categories.

You can assume relationships as the rules of connection between tables which can control how data filters, visuals, and calculations flow through your data model.

Types of Relationships:

Power BI supports four types of relationships between tables:

1. One – to – Many (1 : *)
2. Many – to – One (* : 1)
3. One – to – One ( 1 : 1)
4. Many – to – Many ( * : *)

1.     One – to – Many (1 : *) :

In this relationship, one record in the first table connects to many records in the second table.

Example:
Each customer can have multiple orders, but each order belongs to only one customer.

In the model view, this appears as 1 --> *

• The “1” side is usually a dimension table (like Customers).
• The “many” side is a fact table (like Orders).

Use case:
When you calculate total sales by customer, Power BI automatically connects Orders with the correct Customer through this one-to-many relationship.

-------------------One teacher, Many students-------------------

2.     Many – to – One (* : 1) :

This is the reverse direction of a one-to-many relationship. It happens when the filter starts from the many side and moves to the one side.

Example:

If you select a particular order in a visual (from the Orders table), Power BI can trace it back to the correct Customer in the Customers table.

What happened here you know, multiple orders (many) point to one customer (one), that is a many-to-one relationship. You will see this concept clearly when you review filter directions in your model.

Power BI allows filters to flow in one direction (many → one or one → many), depending on how your relationship is defined.

-------------------Many students connecting back to One teacher-------------------

3.     One – to – One (1 : 1) :

In this relationship, each record in one table matches exactly one record in another table.

Example:
Assume, you have a Customer table and a Customer Profile table. Each customer ID appears only once in both tables.

Use case:
Useful when you split a large table into smaller parts for better performance.

------------------- Best friend have only One for each-------------------

4.     Many – to – Many (* : *) :

In this relationship, both tables can have repeating values in their key columns

Example:
Imagine you are a student and you want to join club, each student can join multiple clubs and each club can have multiple students.

Power BI manages this scenario using a bridge table, which maps each student to each club.

Use case:
Many-to-many relationships should be handled carefully. Always ensure bridge tables or unique identifiers are in place to avoid incorrect totals.

------------------- A big students fest-------------------

Cross Filter Direction and How Data Flows:

Relationships also determine how filters move between the tables. There are two types of cross filter direction.

1. Single Direction
2. Both Direction

1.     Single Direction :

• Filters flow from one table to another, for example: from Customer to Orders.
• This is the default and most efficient setup.

2.     Both Direction/Bidirectional :

• Filters flow both ways between tables.
• Used when both tables should influence each other, for example: Region and Sales.

Important: Use bidirectional filters only when necessary. Too many can cause circular dependencies or performance issues.

So far, we explored the active relationships. Now we can explore Inactive and Virtual relationships.

Inactive and Virtual Relationships:

Inactive Relationship:

Sometimes, two tables can have more than one possible relationship. At that time, you can create a inactive relationship by using disable the Make this relationship active option in the model properties section/edit relationship. Now you can think how can we filter the data, here comes up the DAX function called USERELATIONSHIP()

Example:

Between the Sales and Date tables, you have one active and one inactive relationship. i.e OrderDate → Date (Active) and DeliveryDate → Date (Inactive). Power BI allows only one active relationship at a time.

If you need the inactive one in a calculation, you can use the USERELATIONSHIP() DAX function:

SalesAmount = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[DeliveryDate], Date[Date]))

Virtual Relationship:

Sometimes, you might not want to define physical relationships in the model, especially you are dealing with disconnected tables and building what-if analysis.

Such kind of cases you can use TREATAS() DAX function without active/inactive relationships.

Example:

Imagine you have a Sales table with ProductID, SalesAmount columns and a Product Selector table with ProductName. But these two tables are not connected in the model.

Now you want to filter your Sales table based on the product selected in the slicer (from Product Selector). Since there is no physical relationship, Power BI won’t automatically know how to filter Sales. In this case you can use TREATAS() function.

SalesAmount = CALCULATE(SUM(Sales[SalesAmount]), TREATAS(VALUES(ProductSelector[ProductName]), Sales[ProductName]))

Best Practices for Managing Relationships:

Do’s	Don’ts
Ensure the “one” side has unique keys	Avoid duplicates on the “1” side
Prefer single-direction filters	Don’t overuse both-direction filters
Use bridge tables for many-to-many	Avoid direct many-to-many joins
Rename relationships clearly	Don’t keep auto-generated names
Review your model regularly	Don’t leave tangled connections

When to Create Relationships Manually:

Power BI can auto-detect relationships, but you should verify or define them manually when required:

• Column names differ (e.g., Cust_ID vs CustomerID)
• You need custom filter direction
• You’re working with bridge tables
• You want to control cardinality for performance

In Power BI or in real life, strong relationships lead to clear communication.

Those lines in your Model View are not just connections, they are the foundation that makes your data come alive and tell accurate stories.

[Nabha-Ahmed]

That's true,thanks for sharing

[Magudeswaran_MR]

@ajaybabuinturi 
Thanks for Sharing the Power BI Relationships, Best Practices and Use case of DAX Functions USERRELATIONSHIP and TREATAS in Calculations. I really appreciate your efforts for Publishing this useful blog.

Thanks

[AbdulBasha]

Good information and useful 

[NagaSuresh]

Thanks for Sharing @ajaybabuinturi 

[ketanTiwariAa]

To every great Power BI reports/dashboards there is how efficiently the model is designed. Thank You Ajay for sharing this very important knowledge with all of us.

[Presi]

Nice content @ajaybabuinturi 

[suparnababu8]

Thanks for sharing!