cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pmcmonag Frequent Visitor
Frequent Visitor

Classifying transactions by date, customer and conditions

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 @Nick_M 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

Membership Example Required Solution.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Classifying transactions by date, customer and conditions

  •  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")

Membership Progression.png

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Classifying transactions by date, customer and conditions

  •  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")

Membership Progression.png

View solution in original post

pmcmonag Frequent Visitor
Frequent Visitor

Re: Classifying transactions by date, customer and conditions

@Nick_M 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

 

Membership Example data model.PNG

Super User
Super User

Re: Classifying transactions by date, customer and conditions

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

Highlighted
pmcmonag Frequent Visitor
Frequent Visitor

Re: Classifying transactions by date, customer and conditions

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]
    )
    )
    )
    membership dax.png

    File: Membership v3

Many thanks,

Patrick

pmcmonag Frequent Visitor
Frequent Visitor

Re: Classifying transactions by date, customer and conditions

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 @Nick_M !

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 498 members 3,928 guests
Please welcome our newest community members: