getting a value in a column based on values of few other columns

[yuvalpi]

Hi,

I have a problem that I cannot resolve.

here is my table:

Date	Student Name	Subject	Test Result
08-Dec-23	Amir A.	Math	80
10-Dec-23	Amir A.	Math	90
05-Nov-23	Baruch B.	History	70
01-Dec-23	Amir A.	Science	65
01-Oct-23	Baruch B.	Math	92
1-Sep-23	Baruch B.	Math	85

I would like to have a column that indicates the last "Test Result" based on the Student Name and Subject.

for Amir A. - Math - I would like to get 90, because the test was done on Dec, 10th

for Baruch B. - Math - I would like to get  92

for Baruch B. - History - I would like to see 70

is this possible using a DAX in a column?

it's OK to get the number 90 for all rows of Amir A. , Math.

Thank you in advance,

Yuval

[Bibiano_Geraldo · Accepted solution]

Create a new column and paste the following expression:
Note: Make sure to change 'Sheet1' to your table name.

Column =

VAR student = Sheet1[Student Name]

VAR subject = Sheet1[Subject]

VAR myDate =

CALCULATE(

    LASTDATE(

        Sheet1[Date]

    ),

    FILTER(

        Sheet1,

        Sheet1[Student Name] = student &&

        Sheet1[Subject] = subject

    )

)

VAR lastTestResult =

CALCULATE(

    MAX(

        Sheet1[Test Result]

    ),

    FILTER(

        Sheet1,

        Sheet1[Student Name] = student &&

        Sheet1[Subject] = subject &&

        Sheet1[Date] = myDate

    )

)

RETURN

lastTestResult

Explanations:

1. Variable Declarations:
• VAR student = Sheet1[Student Name]: Stores the student's name from the "Student Name" column.
• VAR subject = Sheet1[Subject]: Stores the subject from the "Subject" column.
• VAR myDate = ...: Calculates the latest date for the specific student and subject using CALCULATE and LASTDATE.
• VAR lastTestResult = CALCULATE(MAX(Sheet1[Test Result]), ...): Calculates the maximum test result for the specific student, subject, and the latest date.
2. Return the Result:
• RETURN lastTestResult: Returns the result stored in the lastTestResult variable as the final output of the DAX expression.

[smpa01 · Accepted solution]

@yuvalpi  you can also do this

Measure = 
VAR _grouping =
    ALLEXCEPT ( 'Table 1', 'Table 1'[Student Name], 'Table 1'[Subject] ) //define GROUPBY columns here 
VAR one =
    CALCULATE ( MAX ( 'Table 1'[Date] ), _grouping ) // what is the max date by the above group
VAR two =
    CALCULATE ( MAX ( 'Table 1'[Test Result] ), _grouping, 'Table 1'[Date] = one ) //what is the test result on max date partioned by grouping
RETURN
    two

[yuvalpi]

Hi smpa01,

I tried your solution and it also works great!

much appreciated!

thank you,

Yuval

[yuvalpi]

Hi Bibiano_Geraldo,

This is amazing!! thank you so much for you quick and proffessional help!

much appreciated,

Yuval

[Bibiano_Geraldo · Accepted solution]

Create a new column and paste the following expression:
Note: Make sure to change 'Sheet1' to your table name.

Column =

VAR student = Sheet1[Student Name]

VAR subject = Sheet1[Subject]

VAR myDate =

CALCULATE(

    LASTDATE(

        Sheet1[Date]

    ),

    FILTER(

        Sheet1,

        Sheet1[Student Name] = student &&

        Sheet1[Subject] = subject

    )

)

VAR lastTestResult =

CALCULATE(

    MAX(

        Sheet1[Test Result]

    ),

    FILTER(

        Sheet1,

        Sheet1[Student Name] = student &&

        Sheet1[Subject] = subject &&

        Sheet1[Date] = myDate

    )

)

RETURN

lastTestResult

Explanations:

1. Variable Declarations:
• VAR student = Sheet1[Student Name]: Stores the student's name from the "Student Name" column.
• VAR subject = Sheet1[Subject]: Stores the subject from the "Subject" column.
• VAR myDate = ...: Calculates the latest date for the specific student and subject using CALCULATE and LASTDATE.
• VAR lastTestResult = CALCULATE(MAX(Sheet1[Test Result]), ...): Calculates the maximum test result for the specific student, subject, and the latest date.
2. Return the Result:
• RETURN lastTestResult: Returns the result stored in the lastTestResult variable as the final output of the DAX expression.