USERELATIONSHIP function In Power

USERELATIONSHIP:

This is particularly useful when you have multiple relationships between the same tables but only one can be active at a time. By default, Power BI allows only one active relationship between two tables, but you can define additional relationships and use USERELATIONSHIP to specify which one to use in your calculations.

Scenario

Suppose you have two tables, Sales and Date, and you want to analyze sales based on two different date columns: OrderDate and ShipDate.

Sales Table:

SaleID	OrderDate	ShipDate	Amount
1	2023-01-01	2023-01-03	100
2	2023-02-01	2023-02-05	200
3	2023-03-01	2023-03-07	150
Date Table:

Date
2023-01-01
2023-01-02
2023-01-03
2023-01-04
...
2023-12-31

Relationships

1. Active Relationship:
   • Sales to Date
2. Inactive Relationship:
   • Sales to Date

Creating Relationships in Power BI

1. Load Data: Load both the Sales and Date tables into Power BI Desktop.
2. Navigate to the Relationships View: Click on the "Model" icon to switch to the Relationships view.
3. Create the Relationships:
   • Create an active relationship between Sales and Date.
   • Create an inactive relationship between Sales and Date.

Using USERELATIONSHIP in a Measure

To create a measure that uses the inactive relationship, you can use the USERELATIONSHIP function.

--Total Sales by Ship Date

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

--This measure calculates the total sales amount using the ShipDate instead of the OrderDate.

--Comparing Sales by Order Date and Ship Date

Total Sales by Order Date = SUM(Sales[Amount])

Total Sales Comparison = 
SUMMARIZE(
    Date,
    Date[Date],
    "Sales by Order Date", [Total Sales by Order Date],
    "Sales by Ship Date", 
    CALCULATE(
        [Total Sales by Order Date],
        USERELATIONSHIP(Sales[ShipDate], Date[Date])
    )
)

Sample Visual Output

CROSSFILTER:

The CROSSFILTER function in DAX is used to control the direction of cross-filtering between two columns. This function is useful when you want to customize the filtering behavior in your calculations.

CROSSFILTER(<column1>, <column2>, <direction>)

• <column1>: The column on the left side of the relationship.
• <column2>: The column on the Right side of the relationship.
• <direction>: The direction of the cross-filtering, which can be one of the following:
  • None
  • OneWay
  • Both

Example Scenario

Creating a Relationship

First, create a relationship between them.

using CROSSFILTER