create boolean out of status column with one status relevant

[ji]

Hello,

I'm trying to create a new dimension in the form of a boolean value. 

The table looks like the following:

cart_id	payment_status
1	pending
1	authorised
1	captured
2	pending
2	failed

with the additional column "success" I wish to mark all "cart_id" with '1' if their payment was authorised and all others with '0'.

The challenge is that one cart_id can have many other payment_status but the only one I care for is "authorised". 

cart_id	payment_status	success
1	pending	1
1	authorised	1
1	captured	1
2	pending	0
2	pending	0

Would be super happy for any advise on how to create this dimension in Power BI

Thanks in advance

[PaulOlding]

This calculated column DAX counts the number of times a cart is authorised.  If any number is returned then the column is set to 1, otherwise 0.

Success =

VAR _AuthorisedCount =

CALCULATE(

COUNTROWS('Table'),

ALLEXCEPT('Table', 'Table'[cart_id]),

'Table'[payment_status] = "Authorised"

)

VAR _Result = IF(_AuthorisedCount, 1, 0)

RETURN

_Result