Forum Discussion
Advocate IVClassifying 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 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
- 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")
5 Replies
- 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")
pmcmonagAnonymous 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