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

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

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