Find the earliest success request

[harinik]

Hello all,

I need to filter or flag the earliest requests that resulted in success. For example a meter id has 2 update requests. First request on 2023-03-10 12:40:34 and its status is "Success". The second update request is at 2023-03-10 15:34:23 and its status is also "Success". Now I want to consider only the first update request and ignore the second request row. Suppose the first update request resulted in "Failure", then it should consider the second update request as the earliest successful request. Here is the sample data

meter id	request id	type	request date	status	earliest date	date  Consider
100A	1A	Update	2023-03-10 12:40	success	2023-03-10 12:40	1
100A	1B	Update	2023-03-10 15:50	Success	2023-03-10 12:40	0
200B	1C	Update	2023-03-15 10:20	Failure	2023-03-15 14:30	0
200B	1D	Update	2023-03-15 14:30	success	2023-03-15 14:30	1

I did a DAX code for this as follows:

earliest success date = VAR request_type = 'table_update'[type]

                                     VAR meterid = 'table_update'[meter_id]

                                     RETURN

                                     MINX(

                                              FILTER('table_update','table_update'[meter_id]=meterid && table_update'[type]=request_type &&   'table_update'[status]="Success"),

                        'table_update'[request_date]  )

To flag the earliest success request: I create another calculated column:

date consider = IF('table_update'[request_date] = 'table_update'[earliest success date],1,0)

But when I implement this code I get the below result:

meter id	request id	type	request date	status	earliest date	date Consider
100A	1A	Update	2023-03-10 12:40	Success	2023-03-10 12:40	1
100A	1B	Update	2023-03-10 15:50	Success	2023-03-10 12:40	0
200B	1C	Update	2023-03-15 10:20	Failure	2023-03-15 10:20	1
200B	1D	Update	2023-03-15 14:30	Success	2023-03-15 10:20	0

Instead I need the earliest success date for meter id 200B to be 2023-03-15 14:30 as it is the first success. 
What am I missing? Could anyone please point me what is missing from this piece of code. Please.
Thank you very much.

[tamerj1 · Accepted solution]

Hi @harinik 
Please try Calculated Column

date consider =
INT (
    'table_update'[request_date]
        = MAXX (
            FILTER (
                CALCULATETABLE (
                    'table_update',
                    ALLEXCEPT ( 'table_update', 'table_update'[meter_id], 'table_update'[type] )
                ),
                'table_update'[status] = "Success"
            ),
            'table_update'[request_date]
        )
)

[harinik]

Hi,
Thanks for quick reply. I tried this and now I get

meter id	request id	type	request date	status	date Consider
100A	1A	Update	2023-03-10 12:40	Success	0
100A	1B	Update	2023-03-10 15:50	Success	1
200B	1C	Update	2023-03-15 10:20	Failure	0
200B	1D	Update	2023-03-15 14:30	Success	0

But I need to flag the first success request. so in this case request id 1A and 1D should have the [date consider] = 1 and request id 1B and 1C should have [date consider] = 0

[tamerj1]

@harinik 

Just need to change the MAXX to MINX. However, I'm wondering why D1 did not receive 1!!

date consider =
INT (
    'table_update'[request_date]
        = MINX (
            FILTER (
                CALCULATETABLE (
                    'table_update',
                    ALLEXCEPT ( 'table_update', 'table_update'[meter_id], 'table_update'[type] )
                ),
                'table_update'[status] = "Success"
            ),
            'table_update'[request_date]
        )
)

[harinik]

@tamerj1 ,

Yes, I changed to MINX. Also there was another value besides the status column that also needs to be checked.

meter id	request id	type	request date	status	value	date Consider
100A	1A	Update	2023-03-10 12:40	Success	close	1
100A	1B	Update	2023-03-10 15:50	Success	close	0
200B	1CC	update	2023-03-15 10:20	Success	open	1
200B	1C	Update	2023-03-15 10:20	Failure	close	0
200B	1D	Update	2023-03-15 14:30	Success	close	0

So, I changed the code to :

date consider_US = INT(

                     'table_update'[request_date]

                     = MINX(

                            FILTER(

                                CALCULATETABLE(

                                    'table_update',

                                    ALLEXCEPT('table_update','table_update'[meter_id],'table_update'[type],'table_update'[status])

                                ),

                                'table_update'[status]="Success"&&'table_update'[value]="close"

                            ),

                            'table_update'[request_date]

                     )

)

Now i get:

meter id	request id	type	request date	status	value	date Consider
100A	1A	Update	2023-03-10 12:40	Success	close	1
100A	1B	Update	2023-03-10 15:50	Success	close	0
200B	1CC	update	2023-03-15 10:20	Success	open	0
200B	1C	Update	2023-03-15 10:20	Failure	close	0
200B	1D	Update	2023-03-15 14:30	Success	close	1

🙂 

Thanks for the solution.