Sequencing a calculated Table

[James13]

Hi guys have a question if it is posible to sequence numbering base on the score column in this kind of calculated table in power bi 

I tried the RANKX formula but as you can see at the photo the duplicates score only has 1 same sequence number

Is it posible to have this kind of view in a calculated table 

[Greg_Deckler · Accepted solution]

@James13 You need the Mythical DAX Index. The Mythical DAX Index - Microsoft Fabric Community

PBIX is attached below signature.

Table 2 = 
    VAR __SourceTable = 'Table'
    VAR __Count = COUNTROWS( __SourceTable )
    VAR __SortText = CONCATENATEX( __SourceTable, [ID], "|" )
    VAR __Table = 
        ADDCOLUMNS(
            GENERATESERIES( 1, __Count, 1 ),
            "__ID", PATHITEM( __SortText, [Value], TEXT )
        )
    VAR __Result =
        ADDCOLUMNS(
            __SourceTable,
            "seq", MAXX( FILTER( __Table, [__ID] = [ID] ), [Value] )
        )
RETURN
    __Result

[Greg_Deckler · Accepted solution]

@James13 You need the Mythical DAX Index. The Mythical DAX Index - Microsoft Fabric Community

PBIX is attached below signature.

Table 2 = 
    VAR __SourceTable = 'Table'
    VAR __Count = COUNTROWS( __SourceTable )
    VAR __SortText = CONCATENATEX( __SourceTable, [ID], "|" )
    VAR __Table = 
        ADDCOLUMNS(
            GENERATESERIES( 1, __Count, 1 ),
            "__ID", PATHITEM( __SortText, [Value], TEXT )
        )
    VAR __Result =
        ADDCOLUMNS(
            __SourceTable,
            "seq", MAXX( FILTER( __Table, [__ID] = [ID] ), [Value] )
        )
RETURN
    __Result

[James13]

is it posbile to sort it to score in desc order? because i want to put another column to identify the Q1 Q2 Q3 Q4

[Greg_Deckler · Accepted solution]

@James13 Yes, the 4th and 5th parameters of CONCATENATEX handle what to sort by and the sort direction.

[James13]

Thanks sir, much appreciated.