Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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! 🤠
It worked wonderfully, thank you very much.
I'm having a little trouble understanding how did you archive it! How does the meassure work? (logic wise)
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! 🤠
User | Count |
---|---|
104 | |
92 | |
88 | |
78 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |