match part of value only

[Anonymous]

Hi Experts, 

I have a challenge where I don't even know where to start.  
I need to create a measure (only measure) to;

Match the 2nd and 3rd characters in table 1[data 1], with the first 2 characters in table 2[data 2].   The result the value in table 3[data 3] 

TIA!! 

table 1		table 2		table 3
data 1		data 2		data 3
XTY		TY XXXXXXX		4
FUY		UY XXXXXX		5
YMN		MN XXXX		6

[az38]

hi @Anonymous 

how is your table 3 is connected with other tables? Is there any relationships?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Hi @az38    -  there is a relationship.  I do not own the data model so cannot check what the relationship is 😞  I know that makes it difficult.  

[az38]

@Anonymous 

maybe. just maybe. you could try smth like new column

Column = 
MIN(
SELECTCOLUMNS(
FILTER(ALL('Table 3'), SEARCH(MID('Table 1'[Data 1],2,2),'Table 2'[Data 2],,0)=1),
"Data 3",
'Table 3'[Data 3]
)
)

do not hesitate to give a kudo to useful posts and mark solutions as solution