USERELATIONSHIP - Use one date table for multiple date columns in a table.

What is USERELATIONSHIP and when to use it?

USERELATIONSHIP is a DAX function that lets you create measures based on inactive relationships between two tables.

Here’s an example of when to use USERELATIONSHIP function: Imagine we have a sales table which has 2 date columns, one for orders and the other is for shipping and we want to use order date as the primary date, so we create an active relationship between order date and our date table. Now, what if we want to count the number of orders shipped starting from the shipping date? That’s when we create an inactive relationship between the shipping date and our date table and make use of the USERELATIONSHIP functio.

 Here is how the formula would look like:

Shipped Orders = CALCULATE (COUNT(Sales [Shipping Date]), USERELATIONSHIP(Sales[Shipping Date], Date[Date])

In the formula above we count the number of shipping and tell the formula to count it from the shipping date using the inactive relationship between shipping date and date table with the use of USERELATIONSHIP formula.

Scroll to Top