Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
yuvalpi
Helper I
Helper I

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

Hi,

I have a problem that I cannot resolve.

 

here is my table:

 

DateStudent NameSubjectTest Result
08-Dec-23Amir A.Math80
10-Dec-23Amir A.Math90
05-Nov-23Baruch B.History70
01-Dec-23Amir A.Science65
01-Oct-23Baruch B.Math92
1-Sep-23Baruch B.Math85

 

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

2 ACCEPTED SOLUTIONS
Bibiano_Geraldo
Super User
Super User

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.
Is this post help you? Please consider to:

Accept as Solution!
Give a Kudo
Follow me on Linkedin

View solution in original post

smpa01
Super User
Super User

@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

 

smpa01_0-1701960853912.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

4 REPLIES 4
smpa01
Super User
Super User

@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

 

smpa01_0-1701960853912.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Hi smpa01,

I tried your solution and it also works great!

much appreciated!

 

thank you,

Yuval

 

 

yuvalpi
Helper I
Helper I

Hi Bibiano_Geraldo,

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

 

much appreciated,

Yuval

Bibiano_Geraldo
Super User
Super User

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.
Is this post help you? Please consider to:

Accept as Solution!
Give a Kudo
Follow me on Linkedin

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.