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://1drv.ms/u/s!AiSRcgO5FUmN8w4UpKd5wMPcvPuI?e=rnLWaF 

 

Test Data :

In the above test data,

 1)     If i select time "11:00 AM",

First, we need to filter unique of each employee  on Max time of "DOJ" Column

Second, after filtering of max time, then filter  "EmpStatus" is Accept only.

If "EmpStatus" is Reject then no need to consider that employee.

So the Final total Records for "11:00 AM" is (1 Record. (i.e) Waugh ==> 10:45 AM). the other record of Jame's Empstatus is Reject. so no need to consider Jame's record. This is till 11:00 AM record.

 

2)     If i select time "03:00 PM",

First, we need to unique filter of each employee on Max time of "DOJ" Column

Second, after filtering of max time, then filter  "EmpStatus" is Accept only.

So the Final total Records for "03:00 PM" is (1 Record. (i.e) again Jame's record should come ==> 02:42 AM). the other record of Waugh Empstatus is Reject. so no need to consider Waugh record. This is till 03:00 PM record

 

like that we need to form the condition.

 

How to do this..?

 

 

 

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.

     

     

     

14 Replies

  • 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


    "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.

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Can you please elaborate what exactly you need to return through the measure , like what is the final output of the measure ?

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

      Hi Anonymous ,

      Thanks for your reply.

      if i select specific time,

      that "selected" time should check "MAX" time of every student from "DOJ" column with "EmpStatus" column value is "Accept" only.

      below is the output for "11:00 AM" and "03:00 PM"

       

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

      Hi Anonymous ,

      Can you pls. update for the same...? i am waiting for this output..