create boolean out of status column with one status relevant

ji — Thu, 01 Jul 2021 15:34:25 GMT

Hello,

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

The table looks like the following:

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

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

Thanks in advance

Re: create boolean out of status column with one status relevant

PaulOlding — Thu, 01 Jul 2021 16:54:42 GMT

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