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
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Thank you for your message.

    I created a measure for application date as well.

    Please check the attached file, and please check if it suits your requirement.

    Thank you.

     

7 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    Status expected measure: =
VAR _latestdate =
    MAX ( Student[Application Date] )
VAR _maxstatuskey =
    MAXX (
        FILTER ( Student, Student[Application Date] = _latestdate ),
        Student[Application Status Key]
    )
RETURN
    IF (
        HASONEVALUE ( Student[Student ID] ),
        CALCULATETABLE (
            VALUES ( 'Status'[Status] ),
            'Status'[Application Status Key] = _maxstatuskey
        )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Jihwan_Kim, thank you for the prompt reply,
      i have another question which is why when i include the application date into the chart it become like this and not showing the latest date it should be? I'm sorry for not telling the requirement earlier but the application date and status code should be shown also. 

      output from your edited pbi (thank you again)

       

       

       

      expected output

       

       

      and i need to use the status code to do the filtering and not by application status key. Hoping you have a nice day and can guide me further on this, thank you !

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        4 years ago

        Hi,

        Thank you for your explanation.

        I fixed the measure.

        By the way, if you want to slice by Status, I think creating a new table is also the way.

        I am not sure how your expected outcome looks like when slicing by STATUS, but please check the attached file.

        I also create a new table.

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Jihwan, the measure works fine as expected, thanks!

      but in my actual file, it was having major performance issues (loading data). i believe this is due to the long dax queries in the measure. And i'm kinda lost on how to simplify it. Appreciate your insights on this again, thank you so much.