March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi all,
data looks as followed:
Customer | Opened on | Case | Status | Timestamp | hh:mm:ss | |||
712 | 18.11.2022 00:00 | 2100045 | In Progress | 18.11.2022 07:28 | ||||
712 | 18.11.2022 00:00 | 2100045 | Closed | 18.11.2022 07:33 | ||||
712 | 18.11.2022 00:00 | 2100146 | In Queue | 18.11.2022 07:40 | ||||
712 | 19.11.2022 00:00 | 2103706 | New | 19.11.2022 07:37 | ||||
712 | 19.11.2022 00:00 | 2103706 | New | 19.11.2022 07:37 | ||||
712 | 19.11.2022 00:00 | 2103706 | Request Processed | 19.11.2022 07:37 | 00:04:00 | <24h | n.FCR | |
712 | 18.11.2022 00:00 | 2100146 | In Queue | 19.11.2022 07:41 | ||||
712 | 18.11.2022 00:00 | 2100146 | In Queue | 21.11.2022 07:44 | ||||
712 | 18.11.2022 00:00 | 2100146 | In Queue | 21.11.2022 07:53 | ||||
712 | 18.11.2022 00:00 | 2100146 | In Progress | 21.11.2022 07:53 | ||||
712 | 18.11.2022 00:00 | 2100146 | Request Processed | 21.11.2022 07:57 | >24h | FCR | ||
124 | 18.11.2022 00:00 | 2100546 | New | 21.11.2022 17:00 | ||||
124 | 18.11.2022 00:00 | 2100546 | Request Processed | 21.11.2022 17:57 | >24h | FCR | ||
234 | 18.11.2022 00:00 | 2100946 | New | 21.11.2022 17:00 | ||||
234 | 18.11.2022 00:00 | 2100946 | Request Processed | 21.11.2022 17:57 | 01:00:00 | <24h | n.FCR | |
234 | 18.11.2022 00:00 | 2100946 | in Queue | 21.11.2022 18:57 | ||||
234 | 18.11.2022 00:00 | 2100946 | Request Processed | 24.11.2022 19:57 | >24h | FCR |
I want to look at customers and his/her cases (One customer can have one-many cases). Once a case reached the status "request processed" at time stamp column it can either be "FCR" or "n.FCR" (FCR standing for first call resolution). A case can reach "request processed" multiple times.
FCR would mean that after the status "request processed" was reached, there were no further changes (no matter which status) to the case within 24 hours.
Therefore at each status "request processed" I want to have a column indicating wether it is "FCR" or "n.FCR". Shown by example column on top in orange color.
Thank you for your help!
Solved! Go to Solution.
Hi @charlineklapu .
According to your description, here is my solution, and please follow these steps.
Firstly, process the data and make sure that your columns "Opened on" and "Timestamp" are Date/time format.
And then use Power Query to add an index column.
And then create a calculated column.
Column =
VAR _a =
CALCULATE (
MAX ( 'Table'[Timestamp] ),
FILTER ( 'Table', [Index] = EARLIER ( 'Table'[Index] ) + 1 )
)
VAR _b =
DATEDIFF ( _a, [Timestamp], HOUR )
VAR _c =
CALCULATE (
MIN ( 'Table'[Timestamp] ),
FILTER (
'Table',
[Customer] = EARLIER ( 'Table'[Customer] )
&& [Timestamp] > EARLIER ( 'Table'[Timestamp] )
)
)
RETURN
SWITCH (
TRUE (),
[Status] = "Request Processed"
&& ISBLANK ( _c ), "FCR",
[Status] = "Request Processed"
&& _b > 24, "FCR",
[Status] = "Request Processed"
&& _b < 24, "n.FCR"
)
Final output:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ xiaosun
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
thanks for your answer.
How do I set up an Index column while using direct query in an sql analysis server datasource? While using this mode and going into power query editor I see no queries.
Thanks for the help again!
Hi @charlineklapu .
According to your description, here is my solution, and please follow these steps.
Firstly, process the data and make sure that your columns "Opened on" and "Timestamp" are Date/time format.
And then use Power Query to add an index column.
And then create a calculated column.
Column =
VAR _a =
CALCULATE (
MAX ( 'Table'[Timestamp] ),
FILTER ( 'Table', [Index] = EARLIER ( 'Table'[Index] ) + 1 )
)
VAR _b =
DATEDIFF ( _a, [Timestamp], HOUR )
VAR _c =
CALCULATE (
MIN ( 'Table'[Timestamp] ),
FILTER (
'Table',
[Customer] = EARLIER ( 'Table'[Customer] )
&& [Timestamp] > EARLIER ( 'Table'[Timestamp] )
)
)
RETURN
SWITCH (
TRUE (),
[Status] = "Request Processed"
&& ISBLANK ( _c ), "FCR",
[Status] = "Request Processed"
&& _b > 24, "FCR",
[Status] = "Request Processed"
&& _b < 24, "n.FCR"
)
Final output:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ xiaosun
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
134 | |
91 | |
90 | |
64 | |
58 |
User | Count |
---|---|
201 | |
137 | |
107 | |
72 | |
68 |