lookup without common column

[Mvumelwano]

HI Team,

Please assist with DAX, I have 2 tables (Table 1 and Table 2) and i need table 3 (see below example). always "a" = "I" and "d" = "m" and "e" = a measure (n+o)

Table1

codes	no
a	1
b	2
c	3
d	4
e	5
f	6

Table2

code	score
k	8
l	9
m	10
n	11
o	12

measure = 23 (11+12)

Table3

code	no	score
a	1	9
b	2
c	3
d	4	10
e	5	23
f	6

Kind regards,

Nocha

[v-diye-msft]

Hi @Mvumelwano 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly.thanks!

[amitchandak]

@Mvumelwano , create a new column in table1 like and then use that in lookup or join

Switch(True(),
[codes] = "a" ,"I" ,
[codes] = "d" , "m",
[codes] = "e" , "a" ,""
)

[Mvumelwano]

@amitchandak Thank you so much , i see i will be able to do a lookup but what about my last value which is a measure ? "e" = 23

please see below table 2.

Kind regards

Mvumelwano

[az38]

@Mvumelwano 

create a Bridge Table by enter data manualy:

Code table1	Code table2
a	I
d	m
e	e

create a relationshhips Table1 - bridge and table2 - bridge

and add what you need to visual

[Greg_Deckler]

Soooo, what is the logic to get from Table1 and Table2 to Table3??

[Mvumelwano]

@Greg_Deckler 

Logic : always "a" = "I" and "d" = "m" and "e" = a measure (n+o)

Thank you

Kind regards,

Mvumelwano

[Greg_Deckler]

Perhaps create a measure like:

New Measure = 
  SWITCH(MAX('Table1'[codes]),
    "a",LOOKUPVALUE('Table2'[score],'Table2'[code],"l"),
    "d",LOOKUPVALUE('Table2'[score],'Table2'[code],"m"),
    "e",[measure],
    BLANK()
  )