Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
Hi,
Please show the exact result which you are expecting.
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.
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
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.
User | Count |
---|---|
106 | |
88 | |
82 | |
76 | |
73 |
User | Count |
---|---|
112 | |
103 | |
96 | |
74 | |
67 |