cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
birdie29
Helper II
Helper II

Lookup in a table based on multiple critera

Hi All

 

I'm trying to create a calulated column that uses the project ID in Table 1 and looks it up in Table 2 however it needs to match not just the 'Project ID' but also the term "Yes" in the 'Latest Reporting Entity' column, to return the value in the 'Reporting Entity' column.

 

Please see the example below. On Table 1 I have entered the Reporting Entity I would expect it to be.

TEST.png

 

Does anyone have any suggestions on how I could solve this? I tried using LOOKUPVALUE function, however as there are multiple lines for some projects I think that's causing it not to work.

 

Thanks very much in advance.

 

Chris

 

 

1 ACCEPTED SOLUTION
v-huizhn-msft
Microsoft
Microsoft

Hi @birdie29,

 

You’d better create a middle table which is used to filter the Table2 where the Latest Reporting is "Yes". Then use the LOOKUPVALUE function. Please try the following solution.

First, create a middle table using CALCULATETABLE function. Please review the formula and screenshot.

Table 3 = CALCULATETABLE(Table2,FILTER(Table2,Table2[Latest Reporting]="Yes"))

1.png


Then create a calculated column using LOOKUPVALUE function, and get desired result.

Reporting Entity = LOOKUPVALUE('Table 3'[Reporting Entity],'Table 3'[Project ID],Table1[Project ID])

2.png

 

Best Regards,
Angelia

View solution in original post

4 REPLIES 4
v-huizhn-msft
Microsoft
Microsoft

Hi @birdie29,

 

You’d better create a middle table which is used to filter the Table2 where the Latest Reporting is "Yes". Then use the LOOKUPVALUE function. Please try the following solution.

First, create a middle table using CALCULATETABLE function. Please review the formula and screenshot.

Table 3 = CALCULATETABLE(Table2,FILTER(Table2,Table2[Latest Reporting]="Yes"))

1.png


Then create a calculated column using LOOKUPVALUE function, and get desired result.

Reporting Entity = LOOKUPVALUE('Table 3'[Reporting Entity],'Table 3'[Project ID],Table1[Project ID])

2.png

 

Best Regards,
Angelia

View solution in original post

Hi @v-huizhn-msft

 

Thanks for your quick response.

 

Just to confirm, am I creating a calulated column within a new table or the exisiting table? Either way when I have tried to create this I get the following error message:

 

Capture2.PNG

 

Thank you

Chris

Hi @v-huizhn-msft

 

Apologies please ignore me, I see how to create a new table now!

 

Thank you

Chris

Hi @birdie29,

I am very gald you have understood creatting a table. If you have any question, please feel free to ask. If you have resolved your issue, please mark the corresponding reply or share your solution for help more people. Thanks for understanding.

Best Regards,
Angelia

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors