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

[saivina2920]

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

[v-nuoc-msft · Accepted solution]

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]

Hi @v-nuoc-msft ,

Thanks for your reply.

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

[v-nuoc-msft]

Hi @saivina2920 

Here is the PBIX file.

[saivina2920]

Hi @v-nuoc-msft ,

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

[v-nuoc-msft]

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]

Hi @v-nuoc-msft ,

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

[saivina2920]

Hi @v-nuoc-msft ,

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]

Hi @v-nuoc-msft ,

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

[saivina2920]

Hi @v-nuoc-msft ,

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]

It is really awesome. It's working.

[punitkhatri]

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

[saivina2920]

Hi @punitkhatri ,

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

[saivina2920]

Hi @punitkhatri ,

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]

can anyone reply for this post.?