Forum Discussion
using inactive relationship
Hello all,
I have a question about how to configure certain relations.
I have data like the following:
"T1" Lookup table for id code
| id | code |
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | f |
"T2" table with id's (irl it has more columns but they are irrelevant for this question)
| id_1 | id_2 |
| 1 | 3 |
| 3 | 2 |
| 4 | 3 |
| 1 | 1 |
| 5 | 5 |
I configured a relation with T1_id and T2_id_1 and another relation between T1_id and T2_id_2
Now what I want to achieve is the following:
| id_1 | code | id_2 | code |
| 1 | a | 3 | c |
| 3 | c | 2 | b |
| 4 | d | 3 | c |
| 1 | a | 2 | b |
| 5 | f | 3 | c |
I tried to do this with the "Related" command but since you can only have on active relation between two tables this only works for either the code of id_1 or the code of id_2.
I could solve this by duplicating the lookuptable and making using one lookuptable per id_1 of id_2 but that doesnt feel really "clean". I was wondering if there is a way to solve this in a better way for example something like the "userelationship" command. Since I would like to choose the relation that the "related" command uses.
I hope someone has an idea to push me into the right direction.
Best regards,
L.Meijdam
Hey,
assuming you want to create a calculated column, you can do this by using this DAX statement, just utilize the DAX function LOOKUPVALUE (it's totally independent from relationships):
code id 2 = LOOKUPVALUE('T1'[code],'T1'[id],'T2'[id_2])Due to the fact that the usage of USERELATIONSHIP needs a CALCULATE that leads to context transition, and so on and so on. I would not recommend to use USERELATIONSHIP but instead plain vanilla LOOKUPVALUE. The DAX would become much more complex and depending on performance impacts can be neglected, due the fact that this calculation happens just during data refresh and not during query execution.
Regards,
Tom
2 Replies
Hey,
assuming you want to create a calculated column, you can do this by using this DAX statement, just utilize the DAX function LOOKUPVALUE (it's totally independent from relationships):
code id 2 = LOOKUPVALUE('T1'[code],'T1'[id],'T2'[id_2])Due to the fact that the usage of USERELATIONSHIP needs a CALCULATE that leads to context transition, and so on and so on. I would not recommend to use USERELATIONSHIP but instead plain vanilla LOOKUPVALUE. The DAX would become much more complex and depending on performance impacts can be neglected, due the fact that this calculation happens just during data refresh and not during query execution.
Regards,
TomHi TomMartens,
Thanks for your reply this solution gives the result I was hoping to achieve, and it is alot cleaner then what I had thought of.
Best regards,
L.Meijdam