Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

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

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

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.