Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Counting records based on dynamic variable

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 idStatus_changed_toModification_datetime
111Full Application01/01/2021 8:45:45 AM
111Validation01/01/2021 8:51:35 AM
111Verification01/01/2021 9:16:45 AM
111Processing01/02/2021 10:45:45 AM
111Not qualified01/02/2021 11:33:45 AM
222Partial Application01/01/2021 5:43:11 AM
222Full Application01/01/2021 6:22:11 AM
222Validation01/01/2021 10:11:11 AM
222Verification01/01/2021 11:43:11 AM
222Processing01/01/2021 1:25:12 PM
222Approved 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 idStatus_changed_toModification_datetimeOrig StatusFinal Status
111Full Application01/01/2021 8:45:45 AMFull Application 
111Validation01/01/2021 8:51:35 AM  
111Verification01/01/2021 9:16:45 AM  
111Processing01/02/2021 10:45:45 AM  
111Not qualified01/02/2021 11:33:45 AM Not Qualified
222Partial Application01/01/2021 5:43:11 AMPartial Application 
222Full Application01/01/2021 6:22:11 AM  
222Validation01/01/2021 10:11:11 AM  
222Verification01/01/2021 11:43:11 AM  
222Processing01/01/2021 1:25:12 PM  
222Approved 01/01/2021 3:13:27 PM Approved

 

 

Any tips and tricks are welcome!

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
    Mohammad_Refaei
    Solution Specialist
    5 years ago

    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.