topic Re: Lookup Value with a filter on another table in DAX Commands and Tips

Lookup Value with a filter on another table

Anonymous — Tue, 26 Oct 2021 10:05:51 GMT

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

Re: Lookup Value with a filter on another table

smpa01 — Mon, 25 Oct 2021 17:04:48 GMT

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)))

Re: Lookup Value with a filter on another table

Anonymous — Tue, 26 Oct 2021 09:45:41 GMT

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")

Re: Lookup Value with a filter on another table

v-yanjiang-msft — Wed, 03 Nov 2021 09:35:26 GMT

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.