Forum Discussion

harinik's avatar
harinik
Frequent Visitor
3 years ago
Solved

Find the earliest success request

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 idrequest idtyperequest datestatusearliest datedate  Consider
100A1AUpdate2023-03-10 12:40success2023-03-10 12:401
100A1BUpdate2023-03-10 15:50Success2023-03-10 12:400
200B1CUpdate2023-03-15 10:20Failure2023-03-15 14:300
200B1DUpdate2023-03-15 14:30success2023-03-15 14:301


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 idrequest idtyperequest datestatusearliest datedate Consider
100A1AUpdate2023-03-10 12:40Success2023-03-10 12:401
100A1BUpdate2023-03-10 15:50Success2023-03-10 12:400
200B1CUpdate2023-03-15 10:20Failure2023-03-15 10:201
200B1DUpdate2023-03-15 14:30Success2023-03-15 10:200

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.


"DAX" "Earliest date"
DAX
  • tamerj1's avatar
    tamerj1
    3 years ago

    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]
        )
)

     

4 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      harinik
      Frequent Visitor
      3 years ago

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

      meter idrequest idtyperequest datestatusdate Consider
      100A1AUpdate2023-03-10 12:40Success0
      100A1BUpdate2023-03-10 15:50Success1
      200B1CUpdate2023-03-15 10:20Failure0
      200B1DUpdate2023-03-15 14:30Success0

       

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        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]
        )
)