Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Getting two latest value two different column in different table

Hi all, I have some difficulties and i need some help. 

 

My task is to get the latest application date and also the latest status from that application date without using the power query, only the dax function(measure or column)

I have two tables which is 

 

Status 

 Student

Data Model

Current Result (not correct)

 

 

Expected Result

 

pbi file 

The issue is, for each student, i need to get the latest application date that he done, and after getting that, i need to filter it by latest status by that date. Two different filtering and i don't know to do it. Hoping that someone could help me, Thanks!

v-cazheng-msft tamerj1 amitchandak SpartaBI lbendlin tamerj1 

Need Help
Show and Tell
  • luohen's avatar
    luohen
    4 years ago

    Hi  Anonymous,

    I created a sample pbix file(download from this link) for you, please check whether that is what you want. You can follow the below steps to get it:

    1. Create a measure as below:

     

    Flag = 
VAR _selstu =
    SELECTEDVALUE ( 'Student'[Student Id] )
VAR _maxdate =
    CALCULATE (
        MAX ( 'Student'[Application Date] ),
        FILTER ( ALLSELECTED ( 'Student' ), 'Student'[Student Id] = _selstu )
    )
VAR _maxstatus =
    CALCULATE (
        MAX ( 'Student'[Application Status Key] ),
        FILTER (
            ALLSELECTED ( 'Student' ),
            'Student'[Student Id] = _selstu
                && 'Student'[Application Date] = _maxdate
        )
    )
RETURN
    IF ( _maxstatus = SELECTEDVALUE ( 'Student'[Application Status Key] ), 1, 0 )

     

    2. Create a table visual and apply a visual level filter with the condition(Flag is 1) on the visual

    Best Regards

2 Replies

  • luohen's avatar
    luohen
    Icon for Resolver II rankResolver II
    4 years ago

    Hi  Anonymous,

    I created a sample pbix file(download from this link) for you, please check whether that is what you want. You can follow the below steps to get it:

    1. Create a measure as below:

     

    Flag = 
VAR _selstu =
    SELECTEDVALUE ( 'Student'[Student Id] )
VAR _maxdate =
    CALCULATE (
        MAX ( 'Student'[Application Date] ),
        FILTER ( ALLSELECTED ( 'Student' ), 'Student'[Student Id] = _selstu )
    )
VAR _maxstatus =
    CALCULATE (
        MAX ( 'Student'[Application Status Key] ),
        FILTER (
            ALLSELECTED ( 'Student' ),
            'Student'[Student Id] = _selstu
                && 'Student'[Application Date] = _maxdate
        )
    )
RETURN
    IF ( _maxstatus = SELECTEDVALUE ( 'Student'[Application Status Key] ), 1, 0 )

     

    2. Create a table visual and apply a visual level filter with the condition(Flag is 1) on the visual

    Best Regards