Forum Discussion

Heonsang's avatar
Heonsang
Frequent Visitor
6 years ago
Solved

RANKX is not sequential

Hello,

 

I tried to create index field ordered by the date but it's not sequential as attached in the screenshot. Do you have any idea how to fix this?

Index = RANKX(Transmission,Transmission[Tx_Date],,ASC)
  • Anonymous's avatar
    Anonymous
    6 years ago

    Here's a measure that does the ranking on the fly:

    [Index] =
if( HASONEVALUE( Transmission[Tx_Date] ),
	RANKX(
		ALLSELECTED( Transmission ),
		Transmission[Tx_Date],
		SELECTEDVALUE( Transmission[Tx_Date] ),
		ASC,
		Dense
	)
)

    The above is relative to the selected dates. If you want to have an index (measure) that's absolute against the whole Transmission table, then you should use this:

    [Index] =
if( HASONEVALUE( Transmission[Tx_Date] ),
	RANKX(
		ALL( Transmission ),
		Transmission[Tx_Date],
		SELECTEDVALUE( Transmission[Tx_Date] ),
		ASC,
		Dense
	)
)

    If you want a calculated column, then... your formula works correctly, which I've checked. If you have a problem, then it most likely means your data type is not correct. Make sure you're using THE CORRECT DATA TYPES.

     

    Best

    D

4 Replies

    • Heonsang's avatar
      Heonsang
      Frequent Visitor
      6 years ago

      There's no filter. I'm curious as duplicate index no is created also.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Heonsang ,

     

     

    You can try,

     

    Index = RANKX( ALL(Transmission),Transmission[Tx_Date],,ASC)

     

    Regards,

     

    Regards,
    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Here's a measure that does the ranking on the fly:

    [Index] =
if( HASONEVALUE( Transmission[Tx_Date] ),
	RANKX(
		ALLSELECTED( Transmission ),
		Transmission[Tx_Date],
		SELECTEDVALUE( Transmission[Tx_Date] ),
		ASC,
		Dense
	)
)

    The above is relative to the selected dates. If you want to have an index (measure) that's absolute against the whole Transmission table, then you should use this:

    [Index] =
if( HASONEVALUE( Transmission[Tx_Date] ),
	RANKX(
		ALL( Transmission ),
		Transmission[Tx_Date],
		SELECTEDVALUE( Transmission[Tx_Date] ),
		ASC,
		Dense
	)
)

    If you want a calculated column, then... your formula works correctly, which I've checked. If you have a problem, then it most likely means your data type is not correct. Make sure you're using THE CORRECT DATA TYPES.

     

    Best

    D