Lookup Value with a filter on another table

[Anonymous]

Hi together, 
I have to check if  in the table "data_Current" the email is matching with an email in the table "users" and it should only match when the User in the table "users" does have an model "IOS" or "Android" in table "devices".

Table Current_Data

Email	Imei	Username
test.1@test.de	1234	test.1
test.2@test.de	12345	test2
test.2@test.de	123456	test2
test.3@test.de	null	test3
test.10@test.de	1212	test100

Table Devices

User-ID	IMEI	SERIAL Number	Model
1	12345	abcde	Andorid
2	22222	abce	Android
3	34333	aaaa	IOS
3	33456	aaaae	Dell
4	12345	ertssd	Dell
5	111111	asdas	IOS

Table Users:

USER-ID	EMAIL	Name
1	test.1@test.de	Meyer
2	test.5@test.de	Lampe
3	test.3@test.de	Anton
4	test.4@test.de	Magda
5	test.100@test.de	Meyer

My suggestion would be to create a new column in the table current_data if the email[current_data] is in the table [users] and has a model ("Android" or "IOS") in Table [devices].

The solution should be:

Email	Imei	Username	NEW COLUMN
test.1@test.de	1234	test.1	Yes
test.2@test.de	12345	test2	No
test.2@test.de	123456	test2	No
test.3@test.de	null	test3	Yes
test.10@test.de	1212	test100	Yes

[v-yanjiang-msft]

Hi @Anonymous ,

According to your description, here’s my solution.

1.Your three tables should have relationship.

2.Create the new column like this.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01]

@Anonymous  difficult to resolve without looking at the data but can you give this a try and let me know

Measure = calculate(MAXX(FILTER(current,current[Email]=
calculate(max(user[userEmail]),filter(devices,device[model]="IOS"||device[model]="android"))),current[Email]),userelationship(current<column>,user<column>,both)))

[Anonymous]

Hi, unfortunately this does not solve the problem. I rather imagine a new column like this statement.

Migrated = IF(ISBLANK(LOOKUPVALUE(devices[imei],devices[imei],'Data_Current'[IMEI])),"No","Yes")