Search code examples
powerbidax

PowerBI DAX: Formula for an "Initial Transaction Flag"


So imagine I have a table "Transactions"

with these columns:

ColumnName DataType
TrnID ID
Customer String
Book Date DateTime
Amount Float
Campaign String

And the following Rows

TrnID Customer Book Date Amount Campaign Expected Measure Column
1 Albert 03-24-23 100 A true false true
2 Albert 03-27-23 50 A false false true
3 Albert 03-29-23 25 A false false false
4 Suzi 03-27-23 50 A true true true
5 Boris 03-27-23 500 A true true true
6 Martha 03-27-23 10 A true true true
7 Anna 03-21-23 50 A true false true
8 Anna 03-22-23 50 A false false true
9 Anna 03-27-23 5 A false false false

QUESTION: How would I get an information in each row, if the transaction is an initial transaction?

That means from a set of transactions with the same [Customer] and the same [Campaign], it is the transaction with the lowest [Book Date].

Unfortunately my experience is limited yet.

ATTEMPT 1) I tried a measure:

InitialTransaction  =

VAR InitialTransaction =
    CALCULATE(
        MIN(Transaction[BookDateMEZ]),
        ALLEXCEPT(Transaction, Transaction[Customer], Transaction[Campaign])
    )
RETURN
    IF(SELECTEDVALUE(Transaction[Book Date]) = InitialTransaction, TRUE(), FALSE())

OUTCOME:

  1. see above in the column "Measure"

ATTEMPT 2) I tried a Clolumn:

InitialTransactionColumn  =

VAR TransactionRank =
    RANKX(
        FILTER(
            Transaction,
            Transaction[Customer] = EARLIER(Transaction[Customer]) &&
            Transaction[Campaign] = EARLIER(Transaction[Campaign])
        ),
        Transaction[Book Date],
        ,
        ASC,
        DENSE
    )
RETURN
    IF(TransactionRank = 1, TRUE(), FALSE())

OUTCOME:

  1. see above in the column "Column"
  2. The Arguments of the EARLIER-function are underlined in red
  3. Despite 2. the DAX editor throws no error and the visual doesn't break.

Solution

  • you can try this t create a column

    Column 2 = 
    VAR _min =
        MINX (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = EARLIER ( 'Table (2)'[Customer] )
                    && 'Table (2)'[Campaign] = EARLIER ( 'Table (2)'[Campaign] )
            ),
            'Table (2)'[Book Date]
        )
    RETURN
        IF ( 'Table (2)'[Book Date] = _min, TRUE (), FALSE () )
    

    enter image description here

    or try this to create a measure

    MEASURE 2 =
    VAR _min =
        CALCULATE (
            MIN ( 'Table (2)'[Book Date] ),
            ALLEXCEPT ( 'Table (2)', 'Table (2)'[Campaign], 'Table (2)'[Customer] )
        )
    RETURN
        IF ( _min = MAX ( 'Table (2)'[Book Date] ), TRUE (), FALSE () )
    

    enter image description here