Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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/20...
Need Help
  • Vvelarde's avatar
    Vvelarde
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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])
return
CALCULATE(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
Cmcmahan's avatar
Cmcmahan
Resident Rockstar
7 years ago

It looks like Vvelarde's DAX solution will work, but I fear that with 40 million+ rows, it will suffer enormous performance problems.   It will only be upon loading the data, but that data load could take a long time.

 

 

Here's the power query I would try to see if it has better performance:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVXSUXLOyUzNK4n3dAGyg0sSS0qLlWJ1opUs9I0MgMjQEihsCJKCihpaYhW1wCaKMMEIiP0wTDDCaoIRVhOMsbrBGNlcCzS1sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{"Client_ID",{"Date", Order.Ascending}}),
    #"PrevTable" = Table.RenameColumns(#"Sorted Rows",{{"Date","PrevDate"}, {"Client_ID", "PrevClient_ID"}, {"Status", "PrevStatus"}}),
    #"Added PrevIndex" = Table.AddIndexColumn(#"PrevTable", "PrevIndex", 1, 1),
    #"Added CurIndex" = Table.AddIndexColumn(#"Sorted Rows", "CurIndex", 0, 1),
    #"Join Prev" = Table.Join(#"Added CurIndex", "CurIndex", #"Added PrevIndex", "PrevIndex", JoinKind.LeftOuter),
    #"Removed Columns" = Table.RemoveColumns(#"Join Prev",{"PrevDate", "PrevIndex"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "StatusOverTime", each 
        if [PrevClient_ID] = [Client_ID] then 
            if [Status] = "S" and [PrevStatus] = "S" then "Active" 
            else if [Status] = "N" and [PrevStatus] = "S" then "Desertion" 
            else if [Status] = "N" and [PrevStatus] = "N" then "Inactive" 
            else if [Status] = "S" and [PrevStatus] = "N" then "Reactivated" 
            else if [Status] = "S" and [PrevStatus] = null then "New Client" 
            else if [Status] = "N" and [PrevStatus] = null then "Prospect" 
            else if [Status] = null and [PrevStatus] = "N" then "Unknown" 
            else if [Status] = null and [PrevStatus] = "S" then "Unknown" 
            else "Error" 
        else null),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"PrevClient_ID", "PrevStatus"})
in
    #"Removed Columns1"

I've also attached a sample .pbix so you can see how it looks on your sample data.