Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I need to create a ranking, that evaluates the Datetime column and reset for each Key:
It should be based on "History New Value Start" and reset back to 1 when there's a new Key. If the first row for the Key isn't "status", enter 0, start counting with 1 when there's the first occurrence of "status"
Here is sample data with the expected result:
Key|History New Value End|History New Value Start|History Field|History New Value|Ranking
SR-771056 | 20.01.2021 8:38:26 | 20.01.2021 8:37:38 | Responsible Team | Service Desk | 0 |
SR-771056 | 20.01.2021 8:38:26 | 20.01.2021 8:38:26 | status | Classification | 1 |
SR-771056 | 05.02.2021 11:17:42 | 20.01.2021 8:38:26 | Responsible Team | PDSA OSB Integration | 1 |
SR-771056 | 05.02.2021 11:17:42 | 05.02.2021 11:17:42 | status | Rejected | 2 |
SRVN-1056 | 29.07.2021 5:06:18 | 29.07.2021 5:04:34 | status | New | 1 |
SRVN-1056 | 29.07.2021 8:22:31 | 29.07.2021 5:04:34 | Responsible Team | Service_Desk (HCVN) | 1 |
SRVN-1056 | 29.07.2021 8:22:20 | 29.07.2021 5:06:18 | status | New | 2 |
SRVN-1056 | 29.07.2021 8:22:31 | 29.07.2021 8:22:20 | status | Classification | 3 |
SRVN-1056 | 29.07.2021 8:31:25 | 29.07.2021 8:22:31 | status | Queued | 4 |
SRVN-1056 | 29.07.2021 8:31:25 | 29.07.2021 8:22:31 | Responsible Team | Application_Support (HCVN) | 4 |
SRVN-1056 | 10.09.2021 9:54:17 | 29.07.2021 8:31:25 | status | Queued | 5 |
SRVN-1056 | null | 29.07.2021 8:31:25 | Responsible Team | Agile_Datamarts (HCVN) | 5 |
SRVN-1056 | null | 03.08.2021 17:44:57 | Time To First Response Status | Met | 5 |
SRVN-1056 | null | 05.08.2021 5:05:20 | Time To Resolution Status | Breached | 5 |
SRVN-1056 | 10.09.2021 9:54:42 | 10.09.2021 9:54:17 | status | In Progress | 6 |
SRVN-1056 | null | 10.09.2021 9:54:42 | status | Rejected | 7 |
Thank you
Solved! Go to Solution.
Hi @vojtechsima ,
Create a column as below:
_Ranking =
VAR _previousField =
CALCULATETABLE (
VALUES ( 'Table'[History Field] ),
FILTER ( ALL ( 'Table' ), 'Table'[Index] < EARLIER ( 'Table'[Index] ) )
)
VAR _index =
CALCULATE (
MAX ( 'Table'[Index] ),
FILTER (
'Table',
'Table'[History New Value] = "new"
&& 'Table'[Index] < EARLIER ( 'Table'[Index] )
)
)
VAR _count2 =
CALCULATE (
COUNT ( 'Table'[History Field] ),
FILTER (
'Table',
'Table'[Index] >= _index
&& 'Table'[Index] <= EARLIER ( 'Table'[Index] )
&& 'Table'[History Field] = "status"
)
)
RETURN
IF (
NOT ( "status" IN _previousField ),
IF ( 'Table'[History Field] <> "status", 0, 1 ),
IF ( 'Table'[History New Value] = "new", 1, _count2 )
)
And you will see:
Pls note that the line 7 should be 1,as it has a new key.
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Hi @vojtechsima ,
Create a column as below:
_Ranking =
VAR _previousField =
CALCULATETABLE (
VALUES ( 'Table'[History Field] ),
FILTER ( ALL ( 'Table' ), 'Table'[Index] < EARLIER ( 'Table'[Index] ) )
)
VAR _index =
CALCULATE (
MAX ( 'Table'[Index] ),
FILTER (
'Table',
'Table'[History New Value] = "new"
&& 'Table'[Index] < EARLIER ( 'Table'[Index] )
)
)
VAR _count2 =
CALCULATE (
COUNT ( 'Table'[History Field] ),
FILTER (
'Table',
'Table'[Index] >= _index
&& 'Table'[Index] <= EARLIER ( 'Table'[Index] )
&& 'Table'[History Field] = "status"
)
)
RETURN
IF (
NOT ( "status" IN _previousField ),
IF ( 'Table'[History Field] <> "status", 0, 1 ),
IF ( 'Table'[History New Value] = "new", 1, _count2 )
)
And you will see:
Pls note that the line 7 should be 1,as it has a new key.
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Hi @vojtechsima ,
Glad to help.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |