Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi everyone,
I got a large dataset(2M> rows) with 5 columns:
I want to rank the within each year, region and course the number of students. For example the first 4 rows have the same year, region and course so they get ranked together.
Secondly, I want a column(or measure) that returns the institution of the highest two ranking institution, all others get the label "Others."
I can't seem to find a rankx that allows the multiple groups. I would really appreciate some help!!
Institution | Year | Region | Course | Number op students | Ranking | Grouping |
30RR | 2019 | Friesland | totaal;008;bbl;4 | 15 | 2 | 30RR |
30RM | 2019 | Friesland | totaal;008;bbl;4 | 12 | 3 | Others |
25PJ | 2019 | Friesland | totaal;008;bbl;4 | 47 | 1 | 25PJ |
08PG | 2019 | Friesland | totaal;008;bbl;4 | 3 | 4 | Others |
25PJ | 2019 | Friesland | totaal;057;bbl;4 | 16 | 1 | 25PJ |
08PG | 2018 | Friesland | totaal;008;bbl;4 | 38 | 1 | 08PG |
25PJ | 2018 | Friesland | totaal;008;bbl;4 | 34 | 2 | 25PJ |
30RM | 2018 | Friesland | totaal;008;bbl;4 | 30 | 3 | Others |
AATR | 2018 | Friesland | totaal;008;bbl;4 | 12 | 4 | Others |
30RM | 2019 | Midden-Utrecht | totaal;057;bbl;4 | 12 | 1 | 30RM |
30RR | 2019 | Midden-Utrecht | totaal;008;bbl;4 | 17 | 2 | 30RR |
30RM | 2019 | Midden-Utrecht | totaal;008;bbl;4 | 39 | 1 | 30RM |
25PJ | 2019 | Midden-Utrecht | totaal;008;bbl;4 | 2 | 5 | Others |
08PG | 2019 | Midden-Utrecht | totaal;008;bbl;4 | 3 | 4 | Others |
AATR | 2019 | Midden-Utrecht | totaal;008;bbl;4 | 15 | 3 | Others |
30RM | 2018 | Midden-Utrecht | totaal;057;bbl;4 | 44 | 1 | 30RM |
30RR | 2018 | Midden-Utrecht | totaal;057;bbl;4 | 7 | 2 | 30RR |
30RR | 2018 | Midden-Utrecht | totaal;008;bbl;4 | 25 | 2 | 30RR |
25LG | 2018 | Midden-Utrecht | totaal;008;bbl;4 | 38 | 1 | 25LG |
@AnniekH96 , If [Number op students] is a column and you need column rank .
If nedd measure rank. Make sure [Number op students] is measure or create a measure and use that in rank = sum(Table[Number op students])
Rankx(filter(Table, [year] =earlier([year]) && [region] =earlier([region]) && [course] =earlier([course]) ),[Number op students])
Rankx(filter(allselected(Table[Year], Table[region], Table[course]), Table[, [year] =earlier([year]) && [region] =earlier([region]) && [course] =earlier([course]) ),[Number op students])
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...
User | Count |
---|---|
57 | |
46 | |
19 | |
16 | |
15 |
User | Count |
---|---|
116 | |
42 | |
40 | |
28 | |
22 |