Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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()
)
Hope someone can give me some help?
Many Thanks !
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.
// 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
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
Apologies, I'm not following you - what would you suggest?
_30mRank = IF (Table1[Column2] > 0,
RANKX( FILTER(Table1, Table1[Column2] > 0.0), Table1[Column2],, ASC, Dense),
BLANK())
Swap in your column, table names
Thanks for your quick response!
Although when I follow your measure, I just get a ranking of 1 for every player?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |