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.
Hello,
I'm looking to get a measure, "Unique Students Last Year," which would be the number of unique records from the previous term (in this case a term is a school year). The scores are based on the school year, and aren't tied to a specific date for regular time intelligence functions.
End result wanted in a table:
School Year Name | Unique Students | Unique Students Last Year |
2020-21 | 4 | |
2021-22 | 2 | 4 |
2022-23 | 1 | 2 |
Simplified model:
Sample Data:
SchoolYear table:
SchoolYearKey | School Year Name | Order |
1 | 2020-21 | -2 |
2 | 2021-22 | -1 |
3 | 2022-23 | 0 |
TestScores table:
ScoreKey | Score | Student | Subject | SchoolYearKey |
1 | 10 | 1001 | Math | 1 |
5 | 15 | 1001 | Math | 2 |
4 | 16 | 1004 | Math | 1 |
3 | 17 | 1003 | Math | 1 |
8 | 18 | 1001 | English | 1 |
2 | 20 | 1002 | Math | 1 |
6 | 20 | 1002 | Math | 2 |
7 | 20 | 1001 | Math | 3 |
9 | 20 | 1001 | English | 2 |
Thanks!
Solved! Go to Solution.
Click here to download the solution
Download PBIX
How iit works ...
Students this year =
DISTINCTCOUNT(TestScores[Student])
Students previous year =
VAR thisref= SELECTEDVALUE(SchoolYear[Order])
VAR previousref= thisref - 1
RETURN
CALCULATE(
DISTINCTCOUNT(TestScores[Student]),
ALL(SchoolYear),
SchoolYear[Order] = previousref)
Please click both the ACCCEPT SOLUTION button and the thumbs up buttons.
If you need more help then please raise a new ticket and quote @speedramps
I will receive an automated notifcation and will try help
Thank you for the kudos.
If you need more help then raise a new ticket and quote @speedramps anywhere in the text, I will then receive an automatic notification and will be delighted to help you again.
Please always try provide example input data as table text (not a screen print) so we can import the data to build a solution for you. You will gain respect and a much quicker and better responses with the more effort put in to describing problems
Click here to download the solution
Download PBIX
How iit works ...
Students this year =
DISTINCTCOUNT(TestScores[Student])
Students previous year =
VAR thisref= SELECTEDVALUE(SchoolYear[Order])
VAR previousref= thisref - 1
RETURN
CALCULATE(
DISTINCTCOUNT(TestScores[Student]),
ALL(SchoolYear),
SchoolYear[Order] = previousref)
Please click both the ACCCEPT SOLUTION button and the thumbs up buttons.
If you need more help then please raise a new ticket and quote @speedramps
I will receive an automated notifcation and will try help
Ahh--All(SchoolYear) was the missing piece for me. Thanks for the quick response!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
127 | |
29 | |
27 | |
24 | |
22 |