topic Re: Use RELATED with USERELATIONSHIP or something similar to use an inactive relationship in DAX Commands and Tips

Use RELATED with USERELATIONSHIP or something similar to use an inactive relationship

Anonymous — Thu, 29 Feb 2024 14:01:46 GMT

So I want to make a calculated column that takes a value in another table and I would usually use the RELATED function but I can't since they are related with an inactive relationship. Is there any way to do that ?

Here is a screenshot of the tables and the inactive relationship :

I want to put the related CODE1 in Daily Operation Table

Re: Use RELATED with USERELATIONSHIP or something similar to use an inactive relationship

123abc — Thu, 29 Feb 2024 14:17:04 GMT

In Power BI, when you have an inactive relationship between tables and you want to use the RELATED function or something similar to access values from another table, you can utilize USERELATIONSHIP in your DAX calculations. Here's how you can achieve this:

Suppose you have two tables: "Daily Operation Table" and "Category Table" with an inactive relationship between them based on the "CODE" column.

Let's say you want to bring the "CODE1" value from the "Category Table" into the "Daily Operation Table" using DAX. You can use the USERELATIONSHIP function to specify the inactive relationship. Here's an example of how you can create a calculated column in the "Daily Operation Table" to achieve this:

RelatedCode1 =
VAR SelectedCategory = 'Daily Operation Table'[Category] // Assuming 'Category' is the related column
RETURN
CALCULATE (
VALUES ( 'Category Table'[CODE1] ),
USERELATIONSHIP ( 'Category Table'[CODE], 'Daily Operation Table'[Category] )
)

In this formula:

We first store the value of the related column from the "Daily Operation Table" in a variable named SelectedCategory.
Then, we use the CALCULATE function to evaluate the expression in a context modified by the USERELATIONSHIP function.
USERELATIONSHIP is used to specify the inactive relationship between the "Category Table" and the "Daily Operation Table" based on the "CODE" column.
Finally, we retrieve the related "CODE1" value from the "Category Table" based on the specified relationship.

Ensure that you replace 'Category' with the actual name of the related column in the "Daily Operation Table" and adjust the column names as per your data model. This DAX expression should allow you to retrieve the related "CODE1" values even with the inactive relationship.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Use RELATED with USERELATIONSHIP or something similar to use an inactive relationship

Anonymous — Fri, 01 Mar 2024 08:35:38 GMT

Hi Anonymous ,

Based on what 123abc said, here are some of my additions.

When dealing with inactive relationships in Power BI or other DAX-based tools, you can use the USERELATIONSHIP function to temporarily activate the inactive relationship for a specific calculation.

The RELATED function is a value function that returns the relevant values from the association table. The USERELATIONSHIP function is used to activate an inactive relationship, and the arguments are the two columns that make up the relationship.

You can read this two documents for a further study: RELATED function (DAX) - DAX | Microsoft Learn and USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Use RELATED with USERELATIONSHIP or something similar to use an inactive relationship

Sandra_aaA — Wed, 31 Jul 2024 14:15:00 GMT

I'm popping in way after you posted this solution to say, thanks! This helped me work out something in my report.

Only thing is I had to add a second filter condition to the Calculate, something like 'Category Table'[CODE]=SelectedCategory. Otherwise I was getting an array instead of one value.