Forum Discussion
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.
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
Read this https://www.sqlbi.com/articles/expanded-tables-in-dax/ article you will get an idea about difference between the two options.
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.
LinkedInHi 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.
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 tableGuys, amitchandak AntrikshSharma Mariusz
I understand how both of the two options are different. The question was about YOUR point of view - which do YOU choose, and why.
