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
Anonymous
Not applicable

RANKX begins on 2 rather than 1

Hi there, 

 

I wish to rank players by their 30m sprint time which dynamically changes when page slicers are applied e.g. Age Group (which is currently it seems to do). However, the results of my RANKX measure starts on 2, rather than 1. 

 

Table Name: 'Sprint' (has an index column which starts at 1) 

 

_30m Time = CALCULATE (
MIN ( Sprint[30m Time]),
FILTER ( ALLSELECTED ( Sprint ),
Sprint[PlayerID] = SELECTEDVALUE ( Sprint[PlayerID] )
)
)
_30mRank = IF(Sprint[_30m Time]>0.00,
RANKX( ALLSELECTED(Sprint),Sprint[_30m Time],,ASC,Dense),
BLANK()
)

dax rankx query.PNG

 

Hope someone can give me some help? 

Many Thanks !

 

7 REPLIES 7
Michael_Burry
New Member

I am also having the same problem, where the RANKX formula starts at 2.

The output of this measure is inconsistent, depending on which category I use to breakdown the result in a matrix.

 

Anonymous
Not applicable

 

// Player must be a dimension connected to Sprints
// through 1:*. Columns in Sprints must be hidden.
// Slicing can only be done through dimensions
// NEVER DIRECTLY ON A FACT TABLE.

// Stick to the rules of dimensional design and
// you'll be producing correct and easy models
// with simple and quick DAX. If you don'... well,
// you'll be in trouble in no time.

// Never filter a table when you can filter a column.
// Please remember this golden rule of DAX programming.

// Measures should NEVER be referenced with their
// table's name. Columns should ALWAYS be referenced
// using their table's name.

[30m Time] =
var __onePlayerVisible = hasonevalue( Player[PlayerID] )
var __30mTime = min( Sprints[30m Time] )
return
	if( __onePlayerVisible, __30mTime )
	
	
[30m Rank] =
var __time = [30m Time]
var __allVisiblePlayersWithNonZeroTime =
	filter(
		allselected( Player ),
		[30m Time] > 0
	)
var __shouldCalculate = __time > 0
var __result =
	rankx(
		__allVisiblePlayersWithNonZeroTime,
		[30m Time], , ASC, DENSE
	)
return
	if( __shouldCalculate, __result )

 

 

This is more or less what you need.

 

Best

D

Anonymous
Not applicable

This is really helpful - thanks for taking the time to explain it all in detail! I'm still getting to grips with ensuring my data model is bullet-proof so this has helped frame it in a logical manner. 

 

Many Thanks

 

Anonymous
Not applicable

Well, your measure

_30mRank = IF(Sprint[_30m Time]>0.00,
RANKX( ALLSELECTED(Sprint),Sprint[_30m Time],,ASC,Dense),
BLANK()
)

says clearly when blank should be displayed but you're ranking against all the visible players...

Best
D
Anonymous
Not applicable

Apologies, I'm not following you - what would you suggest? 

 

 

HotChilli
Super User
Super User

_30mRank = IF (Table1[Column2] > 0,
    RANKX( FILTER(Table1, Table1[Column2] > 0.0), Table1[Column2],, ASC, Dense),
    BLANK())

Swap in your column, table names

Anonymous
Not applicable

Thanks for your quick response! 

 

Although when I follow your measure, I just get a ranking of 1 for every player? 

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.

Top Solution Authors