searching for value in other table

[jwi1]

Hi,

Hope someone can help me with the following.

I have 2 tables, see below.

I want to have a measure for table 1 which gives me the stock for each partnumber_sup from table 2.

As a joint is not advisable (the partnumber_sup gives a many to many relation and I have many other partnumbers in the same tables), I need some help.

Thanks!

table 1

warehouse	partnumber_sup
0	003065J
100	003065J
204	003065J
208	003065J
212	003065J
214	003065J
216	003065J
222	003065J
224	003065J

table 2

warehouse	partnumber_sup	stock
0	003065J	2
204	003065J	1
208	003065J	3
212	003065J	0
214	003065J	1
216	003065J	3
222	003065J	2
224	003065J	1
417	003065J	0

[az38 · Accepted solution]

Hi @jwi1 

try like a

Measure = 
var _currentPartnumber = MAX(Table1[partnumber_sup])
RETURN
CALCULATE(DISTINCTCOUNT(Table2[stock]), FILTER(ALL(Table2), Table2[partnumber_sup] = _currentPartnumber) )

[jwi1]

Hi @az38 , Thanks for yor quick reply.

Decided to use the advise from Greg, the LOOKUPVALUE function.

Will try your advise later.

have a good weekend, John

[Greg_Deckler]

You could create a bridge table. But, you could also use LOOKUPVALUE or MAXX(FILTER(...),...) @jwi1

[jwi1]

@Greg_Deckler 

Hi Greg, tried LOOKUPVALUE and it worked fine!

Thanks