Forum Discussion
Lookup Value with a filter on another table
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
| Imei | Username | |
| [email protected] | 1234 | test.1 |
| [email protected] | 12345 | test2 |
| [email protected] | 123456 | test2 |
| null | test3 | |
| 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 | Name | |
| 1 | [email protected] | Meyer |
| 2 | [email protected] | Lampe |
| 3 | [email protected] | Anton |
| 4 | [email protected] | Magda |
| 5 | [email protected] | 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:
| Imei | Username | NEW COLUMN | |
| [email protected] | 1234 | test.1 | Yes |
| [email protected] | 12345 | test2 | No |
| [email protected] | 123456 | test2 | No |
| null | test3 | Yes | |
| 1212 | test100 | Yes |
3 Replies
smpa01Community Champion
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)))
v-yanjiang-msftCommunity Support
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.