Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
CALCULATE ( SUM ( Results[Score] ), FILTER ( ALLEXCEPT ( Results, Results[Assessment] ), SUMX ( FILTER ( Results, Results[Category] = "Core" && Results[Category] = EARLIER ( Results[Category] ) && Results[Student] = EARLIER ( Results[Student] ) && ( ( Results[Score] < EARLIER ( Results[Score] ) ) || ( ( Results[Score] = EARLIER ( Results[Score] ) && Results[Result_ID] < EARLIER ( Results[Result_ID] ) ) ) ) ), Results[Score] ) ) )
In this scenario, if you want to get the top 2 scores context without using TOPN, you need to use RANKX() to filter top 2 context.
You can create a rank measure like below:
Score Rank = RANKX ( ALLEXCEPT ( Table, Table[Student], Table[Assessment], Table[Category] ), CALCULATE ( SUM ( Table[Score] ), FILTER ( Table, Table[Category] = "Core" ) ), , DESC, DENSE )
Then you can create a measure to calculate the top 2 scores based on above rank:
Top 2 Scores = CALCULATE ( SUM ( Table[Score] ), FILTER ( Table, [Score Rank] <= 2 ) )
Regards,
Hi Simon
Thanks for looking at this.
The Top 2 Scores measure just seems to be returning all the scores, however?
Should I be inserting it into my original formula somewhere rather than using it as a standalone measure?
OK, here's another attempt at trying to get my head round how to do this.
I've simplified things by just looking at the Result_ID so there's no need to do any additional ranking.
I can count the number or records with a lower Result_ID then the current iterated row of the outer FILTER, this formula just sums the total of those records. What I want to do is sum the score from the outer FILTER only where the count of the records in the inner FILTER is 2 or fewer.
CALCULATE ( SUM ( Results[Score] ), FILTER ( ALLEXCEPT ( Results, Results[Assessment], Results[Student] ), COUNTROWS ( FILTER ( Results, Results[Result_ID] > EARLIER ( Results[Result_ID] ) && Results[Category] = "Core"
I get that COUNTROWS in the formula above is just a placeholder but I really want to be able to say COUNTROWS[...] = 2 - there must be a way to do this somehow?
@cesarowicz Did you resolve it? I have the same problem that you, and i be able to resolve using measure too.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |