Classifying transactions by date, customer and conditions

[pmcmonag]

Hi, 

I'm struggling to work through a solution to produce the results as in the 'Membership Progression' column below. Remove Filter Context to give aggregated count in table @Anonymous helped me with a similar example to calculate the 'Total Membership Transactions per Customer'. Simple logic from in a measure using this column  if > 1, then the Customer has had more than one membership transactions, therefore a progression. But I would like to have further logic, and unsure how to approach with dates and conditions and whether this is an EARLIER() problem, whether solved in a measure or calculated column. Required Logic: 

• A customers first entry, MIN(Date) of their transactions would be defined as 'Joined'. 
• Each succesive transaction where 'Membership Stage' (Column E) increases (by one or more) is defined as a 'Progression'. 
• If there is a successive transaction but the 'Membership Stage' stays the same (.i.e transaction ID 11) it will be defined as a 'Renewal'. 

  pbix : 
MembershipExample

Thanks

[Anonymous · Accepted solution]

•  I grabbed the # from the loyalty stage #. Easiest to do in Power Query
• Created an index column that for each customer so we know what "previous" is

Index = 
var __CurrentID= MembershipTransactions[CustomerID]
var __CurrentDate= MembershipTransactions[Date]
RETURN 

CALCULATE(
    COUNTROWS(MembershipTransactions),
    FILTER(
        MembershipTransactions,
        __CurrentID= MembershipTransactions[CustomerID]
        && __CurrentDate >= MembershipTransactions[Date]
    )
    )

• Using those pieces of information, we can use the following calculated column:

Member Progression = 
var __CurrentID= MembershipTransactions[CustomerID]
var __CurrentLotaltyStage= MembershipTransactions[LoyaltyStageNum]
VAR __CurrentIndex = MembershipTransactions[Index]


var __PreviousLoyaltyStage=
    CALCULATE(
        MIN( MembershipTransactions[LoyaltyStageNum] ),
        FILTER(
            MembershipTransactions,
            __CurrentID = MembershipTransactions[CustomerID]
            && __CurrentIndex = MembershipTransactions[Index]+1

        )
    )
RETURN

SWITCH(
    TRUE(),
    __CurrentIndex=1,"Joined",
    __CurrentLotaltyStage> __PreviousLoyaltyStage,"Progression",
    "Renewed")

[pmcmonag]

@Anonymous Great, thanks for your time! Unfortunately for me, when I tried to then take the solution to my real data I ran into a 'circular dependency' error, which I'm struggling to see where that is coming into play. The real data model is set out slighlty differently, and I have managed to re-create this and the error in an updated example. So the Transaction Fact table actually contains many different 'Transaction Types', and then there are Transactions Attributes (many different columns for the different types). In the example I have added just one row to the Fact table of type 'Meeting' and then the lookup would pull in the meeting detail. 

Therefore I've created the Index in Attributes table, which works fine, but it's the CALCULATE in the Progression column which seems to throw up the dependency. I can't quite see why this would be; maybe that I'm trying to filter the Fact table by conditions in the Attributes table in that expression. Or maybe that I haven't succesfully filtered out the one 'Meeting' transaction and therefore the loyalty stage number for that is a blank? (There are some blanks in the attributes table in the real table). 

Any further advice would be hugely appreciated!

Membership Example 2

[Anonymous]

Just quickly, what if you change that 1:1 bidirectional relationship to a 1:M single?

[pmcmonag]

so presumably 1:M single  TransactionsFact --> TransactionsAttributes? It does work to an extent in that the error no longer arises, but seems to introduce other elements I can't resolve: 

• In picture below TransactionID 11 now comes up as a Progression, rather than Renewed as per the original logic. 
• An index is being created for a different transaction type which I thought I had filtered just for "Membership" in Line 12 of the editor. 
• The original dax measure solution in the other post now seems to multiply out when adding into a table. 
• Total Membership Transactions per Customer =

  IF(

  COUNTROWS('Transactions Fact') >0,

  CALCULATE(

  COUNTROWS('Transactions Fact'),'Transactions Fact'[TransactionType]="Membership",

  FILTER(

  ALL('Transactions Fact'),

  MAX( Customers[CustomerID]) = 'Transactions Fact'[CustomerID]

  )

  )

  )

  
  
  File: Membership v3

Many thanks,

Patrick

[pmcmonag]

Update:  resolved the dax measure by wrapping 

IF(

COUNTROWS('Transactions Fact') >0 around 'Transactions Attributes' instead. 

so actually the only issue now, is an Index gets created for TransactionID = 12, which then drives it in the SWITCH statement later to become "Joined". So I'm just trying to remove that now, though the 

'Transactions Fact'[TransactionType]="Membership" in the FILTER statement doesn't appear to resovle it......

Further Update: 

'Transactions Fact'[TransactionType]="Membership" in the FILTER statement was actually causing the Index in TransactionId=11 to evaluate to 1, whereas if removed it evaluates to 2. The logic in the switch in the progression still works fine to evaluate to "Renewed" as wanted! 

I guess to only have this index evaluating for "Membership" types only, but maybe I can figure that last bit out now! Thanks for your help and time on this @Anonymous !