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

 

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

     

6 Replies

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

         

  • Uzi2019's avatar
    Uzi2019
    Community Champion
    2 years ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.