Forum Discussion
Client status over time
Hi,
I have a huge problem trying to get the client status over time.
Imagine a table like this:
| Date | Client_ID | Status |
| 8/20/2019 | 1 | S |
| 8/19/2019 | 1 | S |
| 8/18/2019 | 1 | S |
| 8/20/2019 | 2 | N |
| 8/19/2019 | 2 | S |
| 8/18/2019 | 2 | S |
| 8/20/2019 | 3 | S |
| 8/19/2019 | 3 | N |
| 8/18/2019 | 3 | S |
My desired variable is "Desertion" which is a change in customer status from S to N, however, if i get that one I could get all the others permutations. That being said, I need to create a column that can track the client status over time (keep in mind that the table has around 40MM rows so keep the calculation simple).
The different variations would be:
IF Past_Status = S and Current_Status = S THEN 'Active'
IF Past_Status = S and Current_Status = N THEN 'Desertion'
IF Past_Status = N and Current_Status = N THEN 'Inactive'
IF Past_Status = N and Current_Status = S THEN 'Reactivated'
IF Past_Status = BLANK and Current_Status = S THEN 'New Client'
IF Past_Status = BLANK and Current_Status = N THEN 'Prospect'
IF Past_Status = N and Current_Status = BLANK THEN 'Unknown'
IF Past_Status = S and Current_Status = BLANK THEN 'Unknown'
I have written this problem several times and havent received an answer with a way to properly do it.
Hope you can help me.
Regards,
IC
Anonymous
Hi, lets try with DAX (in Power Query will be a excellent solution too)
1, Create a Calculated Column for Rank
Ranking = VAR _ClientID = 'Table'[Client_ID] RETURN RANKX ( FILTER ( 'Table'; 'Table'[Client_ID] = _ClientID ), 'Table'[Date], , ASC )2, Create a Calculated Column for RankPrev
RankingPrev = 'Table'[Ranking]-1
3. Create a Calculated Column for Previous Status
PastStatus = VAR _ClientID='Table'[Client_ID] VAR _RnkPrev='Table'[RankingPrev] RETURN CALCULATE(VALUES('Table'[Status]),FILTER(ALL('Table'),'Table'[Ranking]=_RnkPrev && 'Table'[Client_ID]=_ClientID))4. Create a Calculated Column for Status to Date
(Review the conditions)
Status_To_Date = SWITCH(TRUE(),'Table'[PastStatus]="S" && 'Table'[Status]="S","Active", 'Table'[PastStatus]="S" && 'Table'[Status]="N","Desertion", 'Table'[PastStatus]="N" && 'Table'[Status]="N","Inactive", 'Table'[PastStatus]="N" && 'Table'[Status]="S","Reactivated", 'Table'[PastStatus]=BLANK() && 'Table'[Status]="S","New Client", 'Table'[PastStatus]=BLANK() && 'Table'[Status]="N","Prospect", 'Table'[PastStatus]="N" && 'Table'[Status]=BLANK(),"Unknown ", "Unknown")
5. Test it with millions of rows
Regards
Victor
Lima - Peru
10 Replies
- Cmcmahan
Resident Rockstar
The issue is that DAX is really bad at figuring out value from "previous row" in an efficent manner. Especially with 40MM rows. Due to the way DAX creates calculated columns, it has to calculate each value from scratch.
There's actually an interesting solution to the problem in Power Query here, where they sort the table, add an index, add a column which is index-1, and join the table to itself based on index and index-1. After getting rid of the rest of the columns, it ends with a current value and previous value in the same row. From there, you can create another conditional column in power query to check if current = N and previous = S and other combinations you want.
For your particular case, I would add in another condition to the join, making sure that the previous client_id is the same as the current, since the index won't reset on client_id change. If you need more help implementing this solution, please ask.
OH YES, I will need help. Actually, someone offered a similar solution with the rank workaround over here:
https://community.powerbi.com/t5/Desktop/Customer-Status-over-time/m-p/760492#M366505
I even already have the rank variable in my table, but I think your solution about bringing the value past value to the actual row would be perfect. Please help me in this matter.
The Rank variable is the following:
```Rank = RANKX
(FILTER('Data clientes lunes por 6 meses',
'Data clientes lunes por 6 meses'[Codigo_Cliente] = EARLIER('Data clientes lunes por 6 meses'[Codigo_Cliente])
&&
'Data clientes lunes por 6 meses'[Codigo_Ingreso] = EARLIER('Data clientes lunes por 6 meses'[Codigo_Ingreso]))
,'Data clientes lunes por 6 meses'[FechaCarga],,ASC,Dense)```And the variable for Last Status is the following:```Ultimo Estado =var mi = MAX('Data clientes lunes por 6 meses'[Rank])returnCALCULATE(MAX('Data clientes lunes por 6 meses'[Es_Cliente]),FILTER(ALLEXCEPT('Data clientes lunes por 6 meses','Data clientes lunes por 6 meses'[Codigo_Cliente]),'Data clientes lunes por 6 meses'[Rank] = mi - 1))```Im almost over deadline and I really need to get this done.Regards,IC- Vvelarde
Community Champion
Anonymous
Hi, lets try with DAX (in Power Query will be a excellent solution too)
1, Create a Calculated Column for Rank
Ranking = VAR _ClientID = 'Table'[Client_ID] RETURN RANKX ( FILTER ( 'Table'; 'Table'[Client_ID] = _ClientID ), 'Table'[Date], , ASC )2, Create a Calculated Column for RankPrev
RankingPrev = 'Table'[Ranking]-1
3. Create a Calculated Column for Previous Status
PastStatus = VAR _ClientID='Table'[Client_ID] VAR _RnkPrev='Table'[RankingPrev] RETURN CALCULATE(VALUES('Table'[Status]),FILTER(ALL('Table'),'Table'[Ranking]=_RnkPrev && 'Table'[Client_ID]=_ClientID))4. Create a Calculated Column for Status to Date
(Review the conditions)
Status_To_Date = SWITCH(TRUE(),'Table'[PastStatus]="S" && 'Table'[Status]="S","Active", 'Table'[PastStatus]="S" && 'Table'[Status]="N","Desertion", 'Table'[PastStatus]="N" && 'Table'[Status]="N","Inactive", 'Table'[PastStatus]="N" && 'Table'[Status]="S","Reactivated", 'Table'[PastStatus]=BLANK() && 'Table'[Status]="S","New Client", 'Table'[PastStatus]=BLANK() && 'Table'[Status]="N","Prospect", 'Table'[PastStatus]="N" && 'Table'[Status]=BLANK(),"Unknown ", "Unknown")
5. Test it with millions of rows
Regards
Victor
Lima - Peru
