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

[mscantle]

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 English scores for those Idno at those dates. The result should be:

I've tried various LOOKUP syntax but can't get it to work.

Thank you in advance for any ideas.

[mscantle]

That's fantastic - many thanks Joe!

[Anonymous]

Hi @mscantle 

Thanks for the reply from @Joe_Barry , please allow me to provide another insight:

First of all, open the Power Query, select the Subject column and click pivot column:

Then select the Grade in Values column and Don't Aggregate in another checkbox:

The result is as follow:

Or you can try this:

First of all, delete the relationship between the two table:

Then add a calculated column in table2:

English Grade =
VAR _vtable =
    FILTER (
        CROSSJOIN (
            SELECTCOLUMNS (
                'Table',
                "_Idno1", 'Table'[Idno],
                "_Date1", 'Table'[Date],
                "_Sub1", 'Table'[Subject],
                "_Grade", 'Table'[Grade]
            ),
            SELECTCOLUMNS ( 'Table2', "_Idno2", 'Table2'[Idno], "_Date2", 'Table2'[Date] )
        ),
        [_Date1] = [_Date2]
            && [_Idno1] = [_Idno2]
    )
RETURN
    SUMX (
        FILTER (
            _vtable,
            [_Idno1] = 'Table2'[Idno]
                && [_Date1] = 'Table2'[Date]
                && [_Sub1] = "English"
        ),
        [_Grade]
    )

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Uzi2019]

Hi @mscantle 

If you want to just have english grade so just apply filter of english on table visual level . it auto shows english grade only.

[Joe_Barry]

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]

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 · Accepted solution]

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