Forum Discussion

birdie29's avatar
birdie29
Icon for Advocate II rankAdvocate II
9 years ago
Solved

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.

 

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

 

 

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

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


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

     

    Best Regards,
    Angelia

4 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

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


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

     

    Best Regards,
    Angelia

    • birdie29's avatar
      birdie29
      Icon for Advocate II rankAdvocate II
      9 years ago

      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:

       

       

      Thank you

      Chris