Forum Discussion

saivina2920's avatar
saivina2920
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

How to get max record based on the condition from all data

I want to get Max time from date column records based on the status of each student for the particular time.   I have attached test PBIX file as mentioned below.   TEST PBIX File Link: https://1d...
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi saivina2920 

    For your question, here is the method I provided:

    Here's some dummy data


    "dimTime"

     

    "Table"

     

    Create measures, query "DOJ" in the selected slicer time

    Doj_Time = 
    var _time = 
        TIMEVALUE(
            SELECTEDVALUE('Table'[DOJ])
        )
    var _dimTime = 
        IF(
            HASONEVALUE(dimTime[Time]),
            VALUES(dimTime[Time]),
            BLANK()
        ) 
    var _status = SELECTEDVALUE('Table'[EmpStatus])
    var doj_time = IF(_time <= _dimTime && _status = "Accept", _time)
RETURN doj_time

     

    Then, query the records based on the "Doj_Time". And filter for records where "result" is not empty in "Filters".

    result = 
    var tt = 
        CALCULATE(
            MAX('Table'[DOJ]),
            FILTER(
                ALL('Table'), 
                TIMEVALUE('Table'[DOJ]) = [Doj_Time]
            )
        )
RETURN 
    IF(
        SELECTEDVALUE('Table'[DOJ]) = tt, 
        SELECTEDVALUE('Table'[DOJ]), 
        BLANK()
    )

     

    Here is the result

     

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

saivina2920's avatar
saivina2920
Icon for Post Prodigy rankPost Prodigy
2 years ago

Hi Anonymous ,

Thanks for your reply.

Can you pls. share sample PBIX file for reference..?

  • saivina2920's avatar
    saivina2920
    Icon for Post Prodigy rankPost Prodigy
    2 years ago

    It is really awesome. It's working.

  • saivina2920's avatar
    saivina2920
    Icon for Post Prodigy rankPost Prodigy
    2 years ago

    Hi Anonymous ,

    I forgot to add one condition here. sorry to ask again.

    Now, we are getting max time from all the student for the selected time with "Accept"Status.

    How to add the additional to get every student of max time with "Accept" condition. (like group by of every student)....?

     

    below is the example record.

    for "11:00 AM", 

     1. James records should not come. because the max time is rejected.

     2. Waugh and Robert records should come for the final record.

     

    How to do this..?

    I want to get all the student record with the above condition.

     

    if this is derived, my requirement is solved. pls. help us to give the idea about this.

     

     

  • saivina2920's avatar
    saivina2920
    Icon for Post Prodigy rankPost Prodigy
    2 years ago

    Hi Anonymous ,

    can you pls. help us to proceed further..? this is important and urgent.

  • saivina2920's avatar
    saivina2920
    Icon for Post Prodigy rankPost Prodigy
    2 years ago

    Hi Anonymous ,

     

    can you pls. give us the idea how to proceed further for the below mentioned queries..? This is urgent.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi saivina2920 

       

      For your question, here is the method I provided:

       

      Here's some dummy data

       

      "Table(2)"

       

      I made the following modifications

       

       

      Date_test = 
    var _time = 
        TIMEVALUE(
            SELECTEDVALUE('Table (2)'[DOJ])
        )
    var _dimTime = 
        IF(
            HASONEVALUE(dimTime[Time]),
            VALUES(dimTime[Time]),
            BLANK()
        ) 
    var doj_time = IF(_time <= _dimTime,_time)
RETURN doj_time

       

       

       

       

       

      Result_TEST = 
    var tt = 
        CALCULATE(
            MAX('Table (2)'[DOJ]),
            FILTER(
                ALL('Table (2)'), 
                TIMEVALUE('Table (2)'[DOJ]) = [Date_test] && 'Table (2)'[StudentName] = MAX('Table (2)'[StudentName])
            )
        )
RETURN 
    IF(
        SELECTEDVALUE('Table (2)'[DOJ]) = tt && SELECTEDVALUE('Table (2)'[EmpStatus]) = "Accept", 
        SELECTEDVALUE('Table (2)'[DOJ]), 
        BLANK()
    )

       

       

       

      Here is the result

       

       

      • saivina2920's avatar
        saivina2920
        Icon for Post Prodigy rankPost Prodigy
        2 years ago

        Hi Anonymous ,

        Thanks for your great help. your test file is working perfect.

        How to show the filter record only (i.e. Result_TEST Record only in the table)