Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vojtechsima
Memorable Member
Memorable Member

Ranking based on Datetime reseting per each Key

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-77105620.01.2021 8:38:2620.01.2021 8:37:38Responsible TeamService Desk0
SR-77105620.01.2021 8:38:2620.01.2021 8:38:26statusClassification1
SR-77105605.02.2021 11:17:4220.01.2021 8:38:26Responsible TeamPDSA OSB Integration1
SR-77105605.02.2021 11:17:4205.02.2021 11:17:42statusRejected2
SRVN-105629.07.2021 5:06:1829.07.2021 5:04:34statusNew1
SRVN-105629.07.2021 8:22:3129.07.2021 5:04:34Responsible TeamService_Desk (HCVN)1
SRVN-105629.07.2021 8:22:2029.07.2021 5:06:18statusNew2
SRVN-105629.07.2021 8:22:3129.07.2021 8:22:20statusClassification3
SRVN-105629.07.2021 8:31:2529.07.2021 8:22:31statusQueued4
SRVN-105629.07.2021 8:31:2529.07.2021 8:22:31Responsible TeamApplication_Support (HCVN)4
SRVN-105610.09.2021 9:54:1729.07.2021 8:31:25statusQueued5
SRVN-1056null29.07.2021 8:31:25Responsible TeamAgile_Datamarts (HCVN)5
SRVN-1056null03.08.2021 17:44:57Time To First Response StatusMet5
SRVN-1056null05.08.2021 5:05:20Time To Resolution StatusBreached5
SRVN-105610.09.2021 9:54:4210.09.2021 9:54:17statusIn Progress6
SRVN-1056null10.09.2021 9:54:42statusRejected7


Thank you

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

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:

vkellymsft_0-1635995505938.png

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!

View solution in original post

3 REPLIES 3
v-kelly-msft
Community Support
Community Support

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:

vkellymsft_0-1635995505938.png

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!

@v-kelly-msft 
Thank you very much, this works.

Hi @vojtechsima ,

 

Glad to help.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.