Forum Discussion
Count Top 1 Rank per day
Hey guys,
I'm trying to get a column that shows the number of days that specific team has ranked 1st
I have a ranking measure based on a score measure that looks like this:
The data has a colum for the Team Name, the points and the date which is filtered using an date slicer.
I'm trying to get a rank for each date within the slicer and count how many times each team has ranked 1st place in each date.
Anonymous
this is the sample data i have used:
result :
power bi result :
measure :
rank measure =
rnk = RANKX( ALLSELECTED(tbl_rank[Team]), [total points], ,DESC )nb of days measure
nb of days n1 = var ds = ADDCOLUMNS( CALCULATETABLE( SUMMARIZE( tbl_rank, tbl_rank[date], tbl_rank[Team] ), ALLSELECTED(tbl_rank[Team]) ), "@r" , [rnk] ) var s = GROUPBY( ds, [@r], tbl_rank[Team], "c", COUNTX(CURRENTGROUP() , 1 ) ) return SUMX( FILTER( s, tbl_rank[Team] in VALUES(tbl_rank[Team]) && [@r] = 1 ), [c])hope this works for you .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
2 Replies
Anonymous
this is the sample data i have used:
result :
power bi result :
measure :
rank measure =
rnk = RANKX( ALLSELECTED(tbl_rank[Team]), [total points], ,DESC )nb of days measure
nb of days n1 = var ds = ADDCOLUMNS( CALCULATETABLE( SUMMARIZE( tbl_rank, tbl_rank[date], tbl_rank[Team] ), ALLSELECTED(tbl_rank[Team]) ), "@r" , [rnk] ) var s = GROUPBY( ds, [@r], tbl_rank[Team], "c", COUNTX(CURRENTGROUP() , 1 ) ) return SUMX( FILTER( s, tbl_rank[Team] in VALUES(tbl_rank[Team]) && [@r] = 1 ), [c])hope this works for you .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠