cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adityavighne
Responsive Resident
Responsive Resident

Lookup value when 2 conditions meet

Hi,

I have two table:

Table 1 contains (ID, Email) and Table 2 contains (ID, Email, Usage)

I want lookup value Usage in Table 1 when two conditionas are meet: ID and Email

 

below is sample dummy data.

 

 

Table 1

IDEmail
1abc.com
2fjfgo.com
3skfj.in
4kfj.pp
5sdfb.com
1sfkh.in
2abc.com
3fjfgo.com
4skfj.in
5kfj.pp
1sdfb.com
2sfkh.in
3abc.com
4fjfgo.com
5skfj.in
1kfj.pp
2sdfb.com
3sfkh.in
4fjfgo.com
5skfj.in

 

Table 2

IDEmailUsage
1abc.comy
2fjfgo.comy
3skfj.iny
4kfj.ppy
5sdfb.comn
1sfkh.inn
2abc.comn
3fjfgo.comn
4skfj.inn
5kfj.ppn
1sdfb.comn
2sfkh.inn
3abc.comn
4fjfgo.comy
5skfj.iny
1kfj.ppy
2sdfb.comy
3sfkh.inn
4fjfgo.comn
5skfj.iny
1 ACCEPTED SOLUTION
Tahreem24
Super User
Super User

@adityavighne  As you have same ID for different email ID so you'll need to create a Key column in both the tables.

Step 1: Create Key column (combining ID and Email) in Table 1.

Key = DATA1[Email]&DATA1[ID]


Step 2: Create the Key Column in Table 2:

Key  = DATA2[Email]&DATA2[ID]

 

Step 3: Create Bridge Table to join these Key columns:

 

Step 4: Then Create calculated column to find relevant usage.

Lookup = IF(DATA2[Key]=RELATED(Bridge[Key]),DATA2[Usage],"-")
 
Output:
Capture.JPG
 
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, and Sales Analysis Report

View solution in original post

2 REPLIES 2
Tahreem24
Super User
Super User

@adityavighne  As you have same ID for different email ID so you'll need to create a Key column in both the tables.

Step 1: Create Key column (combining ID and Email) in Table 1.

Key = DATA1[Email]&DATA1[ID]


Step 2: Create the Key Column in Table 2:

Key  = DATA2[Email]&DATA2[ID]

 

Step 3: Create Bridge Table to join these Key columns:

 

Step 4: Then Create calculated column to find relevant usage.

Lookup = IF(DATA2[Key]=RELATED(Bridge[Key]),DATA2[Usage],"-")
 
Output:
Capture.JPG
 
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, and Sales Analysis Report

View solution in original post

CBO
Frequent Visitor

Hi,

I guess you could consider the brute force of creating a new hybrid key in each table by concatenating the ID and Email fields and then narrowing back the problem to a lookup on a single key with LOOKUPVALUE.

Curious to see if there's something more elegant.

Best

CBO

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.