CD000001
CD000001

Reputation: 3

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.

Upvotes: 0

Views: 15

Answers (1)

Ryan
Ryan

Reputation: 2482

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

Upvotes: 0

Related Questions