cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Continued Contributor

## Calculated columns: USERELATIONSHIP or LOOKUPVALUE?

Imagine 2 tables having 2 relationships with each other (classic case would be the Date table and some other having 2 date columns)

What would you choose and why:

• Having inactive relationships and using USERELATIONSHIP
• Not having relationships and using LOOKUPVALUE

This question came up after receiving this answer: https://community.powerbi.com/t5/Desktop/using-inactive-relationship/m-p/1196978#M535905 which suggests having inactive relationships in that case.

1 ACCEPTED SOLUTION
Community Support

Hi @Zyg_D ,

>>Imagine 2 tables having 2 relationships with each other (classic case would be the Date table and some other having 2 date columns) .

For this question,  I will recommend you choose the first one "Having inactive relationships and using USERELATIONSHIP", which you may create an active relationship on Date[Date] and 'fact table'[Date1] ( note that the [Date1] could be the frequently used one. ),  and then create an inactive relationship on Date[Date] and 'fact table'[Date2] . Once you need the relation about 'fact table'[Date2] to create column or measure, you may use codes like below using USERELATIONSHIP .

``Month= CALCULATE (VALUES ( Date[Month] ),USERELATIONSHIP ( Date[Date], 'fact table'[Date2] ))``

As you thought, at least one active relationship should be created if the two tables need to be used in formulas or further analysis. The LOOKUPVALUE function can be used when there is a relationship or not, while it may be ineffective when there are many matched values, and there will return two matched 'Color'[Color] as "White" and "Green" shown in picture below in your original thread using inactive relationship .

For your original thread using inactive relationship , the RELATED function is suitable in the scenario. While you may create active relationship on 'Toy'[ColorID] and 'Color'[ColorID] , instead of creating relationship between 'Pet' and 'Color'. In this way, a linked relation like below green one will be generated.  Note that the RELATED function only can be used in Many to One relationship, like RELATED([one side Field]).

Best Regards,

Amy

Community Support Team _ Amy

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

5 REPLIES 5
Community Support

Hi @Zyg_D ,

>>Imagine 2 tables having 2 relationships with each other (classic case would be the Date table and some other having 2 date columns) .

For this question,  I will recommend you choose the first one "Having inactive relationships and using USERELATIONSHIP", which you may create an active relationship on Date[Date] and 'fact table'[Date1] ( note that the [Date1] could be the frequently used one. ),  and then create an inactive relationship on Date[Date] and 'fact table'[Date2] . Once you need the relation about 'fact table'[Date2] to create column or measure, you may use codes like below using USERELATIONSHIP .

``Month= CALCULATE (VALUES ( Date[Month] ),USERELATIONSHIP ( Date[Date], 'fact table'[Date2] ))``

As you thought, at least one active relationship should be created if the two tables need to be used in formulas or further analysis. The LOOKUPVALUE function can be used when there is a relationship or not, while it may be ineffective when there are many matched values, and there will return two matched 'Color'[Color] as "White" and "Green" shown in picture below in your original thread using inactive relationship .

For your original thread using inactive relationship , the RELATED function is suitable in the scenario. While you may create active relationship on 'Toy'[ColorID] and 'Color'[ColorID] , instead of creating relationship between 'Pet' and 'Color'. In this way, a linked relation like below green one will be generated.  Note that the RELATED function only can be used in Many to One relationship, like RELATED([one side Field]).

Best Regards,

Amy

Community Support Team _ Amy

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

Continued Contributor

I understand how both of the two options are different. The question was about YOUR point of view - which do YOU choose, and why.

Community Champion

Hi @Zyg_D

Looking at the link to the post I would change the relationships from bi-directional with a single direction, this would allow you to have all the relationships active and use the dimensions to propagate the filters.

if you have a table with 2 dates and one date dimension then you can use inactive relationships and USERELATIONSHIP to activate the second or in some cases duplicate the date dimension table.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Super User

Read this https://www.sqlbi.com/articles/expanded-tables-in-dax/ article you will get an idea about difference between the two options.

Super User

@Zyg_D , related need active relation and work when 1 to M .

This how you move data across table

example : Copy City wthout using related or across unrelated tables
City Name = maxx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City]) // New column in sales

LookupValue //wthout using related or across unrelated tables
Month Name = LOOKUPVALUE('date'[Month Year],'date'[Date],Sales[Sales Date]) // New column in sales table

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors
Top Kudoed Authors