Forum Discussion

mscantle's avatar
mscantle
Helper I
2 years ago
Solved

Lookup value in another table using multiple criteria, including a fixed value

Hi all,   I have one table that looks like this: And another table like this:   The two tables are related via Idno. I want to create a column in the first table that shows the Engl...
Need Help
  • Joe_Barry's avatar
    Joe_Barry
    2 years ago

    Hi mscantle 

    I adapted the measure as i forgot the date

    I just tested and it works for me

     

    Please try again

    English Grade = 
CALCULATE (
    SUM('Table1'[Grade]), 
    FILTER (
        'Table1',
        'Table1'[Idno] = 'Table2'[Idno]
        && 'Table1'[Date] = 'Table2'[Date]
        && 'Table1'[Subject] = "English"))

     

Joe_Barry's avatar
Joe_Barry
Solution Sage
2 years ago

Hi mscantle 

 

Give this a try as a custom column.

 

English Grade = 
CALCULATE (
    SUM('Table2'[Grade]), 
    FILTER (
        'Table2',
        'Table2'[Idno] = 'Table1'[Idno]
        && 'Table2'[Subject] = "English"))

 

 

Hope this helps

 

Joe

mscantle's avatar
mscantle
Helper I
2 years ago

Thanks Joe,

I tried this but I get an error message saying "A single value for 'Idno' in table 'Table2' cannot be determined. This can happen when a measure formula refers to a clumn that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

I thought this might be because there is an English grade for each date for each Idno, so tries this:

 

English Grade=
CALCULATE(
    SUM('Table2'[Grade]),
    FILTER (
        'Table2',
        'Table2'[Idno] = 'Table1'[Idno]
        && 'Table2'[Date] = 'Table1'[Date]
        && 'Table2'[Subject]= "English"))
 
However, this gave the same error message.
  • Joe_Barry's avatar
    Joe_Barry
    Solution Sage
    2 years ago

    Hi mscantle 

    I adapted the measure as i forgot the date

    I just tested and it works for me

     

    Please try again

    English Grade = 
CALCULATE (
    SUM('Table1'[Grade]), 
    FILTER (
        'Table1',
        'Table1'[Idno] = 'Table2'[Idno]
        && 'Table1'[Date] = 'Table2'[Date]
        && 'Table1'[Subject] = "English"))