My goal is to get “Completed Courses” by student and then be able to use things like, Academic Year (to see the number of courses completed in a given year), Gender, Age Group, Academic Career, Academic Plan, to see if there are any patterns between any of these factors and the number of completed courses.
I have this information by using a Calculated Table, I just didn’t know if there is a better approach in getting this.
You could just wrap that table in COUNTROWS(…) and place this into a measure.
In future, need more information in each post. Images etc of the model, the visual you need etc. All of this matters for every calculation and will up answers. Thanks
Sorry about that. I struggle with what details are essential versus not. So imaging the COUNTROWS(…) measure.
I have a measure that counts total registrations which looks like : Total Registrations = COUNTROWS(Registrations).
So would the count of registrations by unique student just be adding a filter into the measure. A new measure that is Total Registrations by Student.
This i probably butchering the syntax all together but thinking out loud:
Total Registrations by Unique Student = COUNTROWS(FILTER(Registrations, Registrations[Student ID]))
Then I can throw this into a table with academic year and the actual student ID so the output looks something like this which counts the completed courses in an academic year.
But now that this is a measure. I need to create a grouping or a way to group this by number of completed courses.
So basically a grouping that is 1 to 25
1
2
3
4
5
6
7
8
9
10
etc.
I cant group a measure so what other factor would I use to create a grouping column?
Visually, below is what I want to create. Where the row header is nothing more than a grouping from 1 to 24. Then when adding the academic year and the Total Registrations by Unique student I would see account of students appear in values next to the number of completed courses in an academic year. So when you look at 2012-13, 2 students completed 21 courses.
I have not. The table was created using an approach that creates a calculated table by summarizing the ‘Registrations’ table. I am trying to do what you did in the video you shared on Dynamic Grouping but I am hitting a mental roadblock with it.
I created a table called 'Count of Courses". But unlike in your table there is no minimum and maximum as the count of courses will equal the one value. Unless I should just remove the “Less than or equal to” and just say “less than.”
Ok I am missing something here major. The output is the total registrations in an academic year instead of an output of total students by the number of completed courses in an academic year.
2016-17 overall total should be 23,260 instead of 86,428 as an example.
There must be a easier way to achieve what you want. I personally wouldn’t take the summarise approach but saying that i still don’t quite understand your situation.
Can you a picture of your columns from your original data source before you summarised it?
I’m guessing there a field for date registered, course completed or not and or completion date?
If there is you could countrows and filer on their completion status.
is there a enrolment id that is specific to that individual?
In the above your min and max would be 0 - 1 / 1 - 2 / 2 - 3
What your attempting to do is quite complex, and you’re really jumping in the deep end without a sound base knowledge of what is happening within the formula and the model.
I’m going to need more context around the tables and current model.
What columns are the calculation being completed over and what do they look like.
So below is a screenshot of the columns in the registration table. Unfortunately I have removed all of the fields that I don’t need and the ones remaining are used in different visuals within the report. But for this specific need You will seen each line is a registration from a student and its connected to the ‘Student’ table
Thinking about the formula you performed in the video I tried to think of it from the perspective of my variables and I think thats where I am going wrong. I am not trying to group by unit price rather the count of completed courses.
So thats where I thought first I needed the formula Total Registrations by Unique Student = COUNTROWS(FILTER(Registrations, Registrations[Student ID]))
But then thinking about total sales I looked at what is my equivalent of sales and that would be Total Registrations = COUNTROWS(Registrations).
After that I think is where I went completely off the rails in over-thinking and unable to find my way back. So that my output results in a table that has a Column for “Completed Courses” and would then tally the number of students that corresponds to that number of completed courses by academic year (academic years as columns.
I factored that I would be calculating Total Registrations but iterate through the Student ID column because I want every unique student ID whose count of registrations is included in one of the ranges established on the ‘Count of Courses’ table.
I wanted to make this approach work if possible because my registrations table is 600,000 plus rows of data. My last resort is to just stick with the summarize table route, which does work but eats up valuable file size.
I think this may be one that I have to abandon. There is no column that says a registration complete because by virtue of the registration being the table (each row is a registration) its a completed registration.
The columns used in this exercise from the massive table or registrations are:
Academic Year (identified as 2015-16, 2016-17, 2017-18…)
Student ID (unique 7 digit number assigned to the student. Each duplication of this number is a registration completed by the student so in an academic year if their ID appears 12 times, that means they took 12 courses. This is counted by academic year.)
Registration Count (this column places a 1 for each registration which is represented by a row so 86,000 lines/rows would have a 1 signifying 86,000 registrations).
I created a dummy file that cuts down to the fields that are being used or need to be used.
The first thing I would do is look at the count of completed courses in an Academic Year broken down by the individual student. Then I would look at it by course then I would look at it by the Term Student Location. But the overall picture is to look at the number of completed courses student within a given academic year.
Your dynamic grouping videos are excellent.
I’m having to really up my game to understand PowerBI, as executing this type of analysis lies at an intersection of so many concepts - the data model, evaluation context etc.
Although I prefer to add tables using DAX, as opposed to the ‘Enter Data’ approach, as its easier to amend the table later on as needed, without upsetting the model.
There are probably other ways, but I’ve been using this code (I found it somewhere) to add tables manually:
I can tweak it as necessary without having to delete the table & re-enter with ‘enter data’
Yep that’s fine, but it is also seriously easy to make small changes to the table you create using ‘enter data’ within the query editor.
Where you idea certainly is better is if those numbers need to be dynamic in some way. You might want to derive then based off percentages for example.
