How to Create a Relationship in Power BI Using DAX

Creating relationships between tables in Power BI is usually as simple as a drag-and-drop. But what happens when you need to connect tables based on multiple different criteria, like an order date and a shipping date? This is where standard relationships fall short. This article will show you how to use DAX functions like USERELATIONSHIP and TREATAS to create flexible, virtual relationships that open up a new level of analysis in your reports.

Why Does Power BI Need DAX for Relationships?

In most cases, you connect your tables in Power BI’s "Model" view. You find a common column, like 'OrderID' in both a sales table and a customer table, and drag one on top of the other to create a link. Power BI uses this physical connection, or relationship, whenever you build visuals.

The problem arises in a few common scenarios:

• Multiple Relationships Between Two Tables: Imagine a master calendar table and a sales table. The sales table has an OrderDate, a ShipDate, and a DueDate. You can create a physical relationship between your calendar's Date column and only one of those sales dates. Tying it to all three would confuse Power BI. Which path should it take for calculations? To solve this, Power BI only allows one relationship to be "active" at a time. The rest are designated as "inactive."
• Different Levels of Granularity: You might have a table of daily sales figures and another table with monthly marketing budgets. Directly linking them is tricky because the units of time (day vs. month) don't match up perfectly.
• Models Without Physical Relationships: In some advanced or unusual data models, it might not be possible or desirable to create a permanent physical link between two tables, but you still need them to filter each other for certain calculations.

DAX functions give you a way to work around these limitations. They let you temporarily activate an inactive relationship or even create a completely virtual one that only exists for the duration of a single calculation.

Method 1: Activating Inactive Relationships with USERELATIONSHIP

The most common scenario you'll face is needing to calculate a metric based on different dates. Using DAX to activate an inactive relationship is the perfect solution. Let’s walk through an example.

The Scenario: Sales by Order Date vs. Ship Date

Say you have a Sales table and a Calendar table. In the Model view, you’ve correctly linked Calendar[Date] to Sales[OrderDate]. This is your primary, active relationship. When you build visuals, all sales will be calculated based on when the order was placed.

But management also wants to see sales based on when the product was shipped. To do this, you would create a second, inactive relationship by dragging Calendar[Date] to Sales[ShipDate]. In the Model view, you'll see this relationship as a dotted line, distinguishing it from the active one (a solid line).

With this inactive link in place, you can now use DAX to bring it to life.

Step-by-Step Instructions

1. Create a Base Measure

First, let’s create the standard sales measure. This measure will respect the default active relationship (based on OrderDate).

Total Sales = SUM(Sales[SalesAmount])

2. Build the USERELATIONSHIP Measure

Next, we'll create a new measure that calculates sales, but this time directs Power BI to use our inactive shipping date relationship. To do this, we combine the CALCULATE function with the USERELATIONSHIP function.

The USERELATIONSHIP function's job is very simple: it tells CALCULATE which relationship to activate. It doesn't do anything on its own.

Sales by Ship Date = 
CALCULATE(
    [Total Sales],
    USERELATIONSHIP(Calendar[Date], Sales[ShipDate])
)

3. Understanding the Formula

Let's break down what's happening:

• CALCULATE: This is one of the most powerful functions in DAX. It modifies the "filter context" of a calculation. In simple terms, it changes the rules for how a value is summed up, counted, or averaged.
• [Total Sales]: This is the calculation we want to perform. We're telling CALCULATE to start with our basic sum of SalesAmount.
• USERELATIONSHIP(...): This is the new rule. For this one measure only, we're telling CALCULATE to ignore the active OrderDate relationship and instead activate the inactive relationship between Calendar[Date] and Sales[ShipDate].

4. See It in Action

Now you can build a table or chart in your report. If you add Calendar[Month] to the axis and add both Total Sales and Sales by Ship Date as values, you'll see two different sets of numbers for each month reflecting the difference between when orders were placed and when they were shipped.

Method 2: Creating Virtual Relationships with TREATAS

Sometimes you need to filter one table with another where no relationship, active or inactive, exists. This can happen with mismatched granularities (e.g., daily data and monthly targets) or tables from completely different parts of your business.

The TREATAS function builds a temporary, virtual relationship between two tables for a single measure.

The Scenario: Applying Monthly Discounts to Daily Sales

Imagine your daily Sales table alongside a simple, disconnected MonthlyDiscounts table that looks like this:

There's no direct way to link these. How do you apply the correct monthly discount to each daily sales transaction?

Step-by-Step Instructions

1. Recognizing the Challenge

We can't just multiply the sale amount by the discount rate because Power BI has no idea which month's discount to apply to which transaction. This is a perfect job for a virtual relationship handled "on-the-fly".

2. Build the TREATAS Measure

Here’s how you'd write the DAX measure to calculate the total discount amount for any given time period. It looks a bit complex at first, but we'll break it down.

Total Discount Amount =
VAR CurrentMonth = VALUES(Sales[MonthName]) 
VAR MonthlyDiscountRate =
    CALCULATE(
        MAX('MonthlyDiscounts'[DiscountRate]),
        TREATAS(
            CurrentMonth,
            'MonthlyDiscounts'[MonthName]
        )
    )
RETURN
    SUMX (
        Sales,
        Sales[SalesAmount] * MonthlyDiscountRate
    )

3. Understanding the Formula

This DAX formula involves several parts that work together:

• VAR CurrentMonth = VALUES(Sales[MonthName]): This line creates a variable named CurrentMonth. VALUES(Sales[MonthName]) returns a distinct list of months currently visible in the filter context. For example, if a table visual is showing sales for 'January', CurrentMonth will contain 'January.'
• VAR MonthlyDiscountRate = CALCULATE(...): Here we calculate the applicable discount rate.
• RETURN SUMX(...): This last part wraps the logic together, multiplying each sale by the corresponding discount rate determined by the virtual relationship.

Key Considerations and Best Practices

• Performance First: Physical relationships in the Model view are always the fastest and most efficient option. Use them whenever you can. Use the USERELATIONSHIP method with inactive relationships as your next best choice. TREATAS is incredibly flexible but can be slower on extremely large datasets.
• Keep Your Model Clear: A documented inactive relationship (the dotted line) is a valuable signpost for anyone else working on your Power BI report. It tells them a connection is possible and intended, even if it’s not always on. A TREATAS function is hidden inside a measure and less discoverable.
• Direction Matters: Filters typically flow "downhill." In our examples, the Calendar filters the Sales table, not the other way around. Keep the direction of your relationships in mind when writing DAX to avoid unexpected results.

Final Thoughts

Power BI’s modeling capabilities are fantastic for building straightforward reports, but business questions are rarely that simple. Knowing how to create virtual relationships with DAX functions like USERELATIONSHIP and TREATAS gives you the control to handle more complex scenarios involving multiple dates, shifting criteria, or mismatched tables. Mastering this technique is a significant step toward becoming a Power BI pro.

While mastering DAX is a powerful skill, it also highlights the steep learning curve required to get complex answers from traditional business intelligence tools. Hours spent Googling formulas and troubleshooting filter contexts can feel unproductive. This is exactly why we built Graphed. We connect to your marketing and sales data sources so you can create dashboards instantly, simply by asking for what you want in plain English. No complex code or modeling required - just live, accurate answers in seconds.