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 ,

 

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)

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

      Hi Anonymous ,

      pls. share the pbix file for Result_TEST row table only.(we already derived in the measure).