Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dax Issue

Hi Everyone           In this screenshot customer order initiated and payment success is in same txn id we want transaction completed or else customer is only order initiated we want payment failed....
Need Help
Show and Tell
Tips and Tricks
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi,Anonymous 

     

    According to your description,I think you can create a column to calculate the desired result,like this:

     

    Column =
VAR a =
    CALCULATETABLE (
        DISTINCT ( 'Table'[TXN-OID] ),
        FILTER ( ALL ( 'Table' ), [TXN-STATUS] = "ORDER_INITIATED" )
    )
VAR c =
    CALCULATETABLE (
        DISTINCT ( 'Table'[TXN-OID] ),
        FILTER ( ALL ( 'Table' ), [TXN-STATUS] = "PAYMENT_SUCCESS" )
    )
RETURN
    IF ( [TXN-OID] IN a && [TXN-OID] IN c, "completed", "payment failed" )

    OR:

    Column 2 =
VAR a =
    COUNTROWS (
        FILTER (
            'Table',
            [TXN-OID] = EARLIER ( 'Table'[TXN-OID] )
                && [TXN-STATUS] = "ORDER_INITIATED"
        )
    )
VAR b =
    COUNTROWS (
        FILTER (
            'Table',
            [TXN-OID] = EARLIER ( 'Table'[TXN-OID] )
                && [TXN-STATUS] = "PAYMENT_SUCCESS"
        )
    )
RETURN
    IF ( b > 0 && a > 0, "completed", "payment failed" )

     

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

d_gosbell's avatar
d_gosbell
Super User
5 years ago

Assuming that your table is called 'Transactions' you could use an expression like the following to find the maximum updated datetime and then lookup the status

 

Payment Status = 
var txnOid = [TXN_OID]
var maxDate = 
    MAXX( 
        FILTER(ALL(Transactions[TXN_OID], Transactions[STATUS_UPDATE_DATETIME]), 
                Transactions[TXN_OID] = txnOid)
    , Transactions[STATUS_UPDATE_DATETIME])
var lastStatus = LOOKUPVALUE(Transactions[TXN_STATUS]
    , Transactions[TXN_OID] , txnOid
    , Transactions[STATUS_UPDATE_DATETIME], maxDate)
return if( lastStatus = "PAYMENT_SUCCESSFUL", "completed", "payment failed")
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Am trying this dax. but am gettting some error