Counting records based on dynamic variable

[abogdanov]

Hello,

I have a table that is a log of changes that our applications go through before they either accepted or denied. What I'm trying to  do here is understand the quality of our applications and customer serivce. Here is what my table looks like:

Application id	Status_changed_to	Modification_datetime
111	Full Application	01/01/2021 8:45:45 AM
111	Validation	01/01/2021 8:51:35 AM
111	Verification	01/01/2021 9:16:45 AM
111	Processing	01/02/2021 10:45:45 AM
111	Not qualified	01/02/2021 11:33:45 AM
222	Partial Application	01/01/2021 5:43:11 AM
222	Full Application	01/01/2021 6:22:11 AM
222	Validation	01/01/2021 10:11:11 AM
222	Verification	01/01/2021 11:43:11 AM
222	Processing	01/01/2021 1:25:12 PM
222	Approved	01/01/2021 3:13:27 PM

so what I'm trying to do is to find a way to tell how many applications go from "partial application"  to "full application"; from "full application" to "validation"  or from "partial application" to "approved" etc. What is a good way to execute this? 

I have tried creating columns that show the first and last status of each id using MIN() but since they are located on different rows, I can't count them.  

Application id	Status_changed_to	Modification_datetime	Orig Status	Final Status
111	Full Application	01/01/2021 8:45:45 AM	Full Application
111	Validation	01/01/2021 8:51:35 AM
111	Verification	01/01/2021 9:16:45 AM
111	Processing	01/02/2021 10:45:45 AM
111	Not qualified	01/02/2021 11:33:45 AM		Not Qualified
222	Partial Application	01/01/2021 5:43:11 AM	Partial Application
222	Full Application	01/01/2021 6:22:11 AM
222	Validation	01/01/2021 10:11:11 AM
222	Verification	01/01/2021 11:43:11 AM
222	Processing	01/01/2021 1:25:12 PM
222	Approved	01/01/2021 3:13:27 PM		Approved

Any tips and tricks are welcome!

[Ashish_Mathur]

Hi,

Please show the exact result which you are expecting.

[Anonymous · Accepted solution]

Hi @abogdanov ,

I doubt the order for From-To type could be reversed, such as from partial application to full application, or it also can be from full application to partial application...

So in order to consider comprehensive, I use CROSSJOIN() to create a FromTo table as shown below:

FromTo =
VAR _from =
    DISTINCT ( SELECTCOLUMNS ( 'Table', "From", 'Table'[Status_changed_to] ) )
VAR _to =
    DISTINCT ( SELECTCOLUMNS ( 'Table', "To", 'Table'[Status_changed_to] ) )
RETURN
    CROSSJOIN ( _from, _to )

Now please follow these steps:

1. Find the first status

From =
VAR _firstdate =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Modification_datetime], TRUE () ),
        ALLEXCEPT ( 'Table', 'Table'[Application id] )
    )
RETURN
    LOOKUPVALUE ( 'Table'[Status_changed_to], [Modification_datetime], _firstdate )

2. Find the last status

To =
VAR _lastdate =
    CALCULATE (
        LASTNONBLANK ( 'Table'[Modification_datetime], TRUE () ),
        ALLEXCEPT ( 'Table', 'Table'[Application id] )
    )
RETURN
    LOOKUPVALUE ( 'Table'[Status_changed_to], [Modification_datetime], _lastdate )

3. Create a measure to count the number of application with each From - To type:

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Application id] ),
    FILTER (
        'Table',
        'Table'[From] = MAX ( 'FromTo'[From] )
            && 'Table'[To] = MAX ( 'FromTo'[To] )
    )
)

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[abogdanov]

Eyelyn, I think your solution is exactly what I needed! One question though, would the From and To formulas fail if a single id has repetitions? let's say one id went from Validation to Verification and then back to Validation

[Mohammad_Refaei]

You can do this with simple and stratighforward approach through adjusting the data model.

Build a calendar table, a table to define the status sequence and simple count.

Please check this sample file.