RANKX is not sequential

[Heonsang]

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 · Accepted solution]

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

[harshnathani]

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)

[Pragati11]

Hi @Heonsang ,

Are there any filters on this visual or on your page?

Thanks,

Pragati

[Heonsang]

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