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:
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:
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 () )
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 () )