using inactive relationship

[Anonymous]

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

[TomMartens · Accepted solution]

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

[Anonymous]

Hi @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