cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Count of Registrations by AY

In the attached file (file link below) I have the student ID, Academic Year of the Registration, Course they registered for in that AY, and the start and end dates of the particular course.  This is a sample file so that I am not releasing real student data.

Each occurrence of the student ID is considered a registration so it's a count instead of a distinct count.

What I am trying to figure out is how to take a count of registrations by student.  The goal is to show the number of registrations by academic year to present in a visual like seen below.  The visual shows the number of courses and the count of students by AY that completed that many courses.  So in the image, you see that 1,560 students completed 1 course during what represents the current AY (2016-2017)

Note: Academic years always start July 1 and end June 30.

1 ACCEPTED SOLUTION
Microsoft Employee

@JMWDBA wrote:

In the attached file (file link below) I have the student ID, Academic Year of the Registration, Course they registered for in that AY, and the start and end dates of the particular course.  This is a sample file so that I am not releasing real student data.

Each occurrence of the student ID is considered a registration so it's a count instead of a distinct count.

What I am trying to figure out is how to take a count of registrations by student.  The goal is to show the number of registrations by academic year to present in a visual like seen below.  The visual shows the number of courses and the count of students by AY that completed that many courses.  So in the image, you see that 1,560 students completed 1 course during what represents the current AY (2016-2017)

Note: Academic years always start July 1 and end June 30.

@JMWDBA

Could you try to create a calculate table

```Table =
SUMMARIZE (
yourTable,
yourTable[Student ID],
"completedCourse", COUNTA ( yourTable[Course] )
)```

And then put the column completedCourse x-axis and SUM(completedCourse) as y-axis?

2 REPLIES 2
Microsoft Employee

@JMWDBA wrote:

In the attached file (file link below) I have the student ID, Academic Year of the Registration, Course they registered for in that AY, and the start and end dates of the particular course.  This is a sample file so that I am not releasing real student data.

Each occurrence of the student ID is considered a registration so it's a count instead of a distinct count.

What I am trying to figure out is how to take a count of registrations by student.  The goal is to show the number of registrations by academic year to present in a visual like seen below.  The visual shows the number of courses and the count of students by AY that completed that many courses.  So in the image, you see that 1,560 students completed 1 course during what represents the current AY (2016-2017)

Note: Academic years always start July 1 and end June 30.

@JMWDBA

Could you try to create a calculate table

```Table =
SUMMARIZE (
yourTable,
yourTable[Student ID],
"completedCourse", COUNTA ( yourTable[Course] )
)```

And then put the column completedCourse x-axis and SUM(completedCourse) as y-axis?

That worked like a charm!!!! THANK YOU!!!!!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.