topic Re: How to create a vlookup between tables to retrieve a text field in DAX Commands and Tips

How to create a vlookup between tables to retrieve a text field

Lobojo — Thu, 16 Feb 2023 08:18:29 GMT

Hi,

I'm trying to create a column in Table 1 that would be filled via a "vlookup" style from Table 2 or Table 3, depending on the 1st value in column # (if 4 -> table 2, if 2 -> table 3)

Which is the proper command to be used and how have to be connected the 3 tables in order that it works properly? I've tried various way without any correct result

I've tried with:

TEXT111 = SWITCH (

TRUE (),

LEFT('Table1'[#],1)=6 , RELATED ('Table3'[Text1]),

LEFT('Table1'[#],1)=4, RELATED ('Table2'[Text1])

)

or with

text1 = switch(true,left('Table1'[#],1)=4,calculate(SELECTEDVALUE(Table2[Text1])),left('Table1'[#],1)=6,calculate(SELECTEDVALUE(Table3[Text1])))

but it doesn't work

Thanks!

Table 1		Table 2		Table 3
#	Text	#4	Text1	#2	Text1
4100		4100	Pippo	2400	Minnie
4200		4200	Pluto	2500	Topolino
2400
2500

Re: How to create a vlookup between tables to retrieve a text field

FreemanZ — Thu, 16 Feb 2023 08:36:35 GMT

hi Lobojo

if Table1 is related with Table2 and Table3, then just add a column in Table1 like:

Text = RELATED(Table2[Text1])&RELATED(Table3[Text1])

it worked like:

Re: How to create a vlookup between tables to retrieve a text field

FreemanZ — Thu, 16 Feb 2023 08:41:27 GMT

or you try like:

Text2 = MINX( FILTER(Table2, Table2[#4] = Table1[#]), [Text1] ) & MINX( FILTER(Table3, Table3[#2] = Table1[#]), [Text1] )

This approach does not rely on relationship.

Re: How to create a vlookup between tables to retrieve a text field

FreemanZ — Thu, 16 Feb 2023 08:43:53 GMT

or you try like:

Text3 = LOOKUPVALUE(Table2[Text1], Table2[#4], Table1[#]) & LOOKUPVALUE(Table3[Text1], Table3[#2], Table1[#])

This approach does not rely on relationship as well.

Re: How to create a vlookup between tables to retrieve a text field

Lobojo — Thu, 16 Feb 2023 10:11:28 GMT

Correct. But the table have to be related 1:1 in order to work. Thanks