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'.
@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).
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......
'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 !