Forum Discussion
Grouping/segmenting a measured column
I've been struggling with this one for a couple of days and I've gotten close a couple of times, but I can never totally figure it out.
I've got a dataset containing session IDs (unique identifier) and student IDs (non-unique). So each record is a session, and a session can only have one student, but that student can attend multiple sessions, like so:
session_id (pk) | student_id 1 | 12345 2 | 12345 3 | 12345 4 | 999 5 | 20611
6 | 20611
Our customers want to know how many students attended what number of sessions,and they want the number of sessions grouped into the following buckets: 1, 2, 3, 4, 5, 6 or more. These categories would be mutually exclusive, so if I'm a student and I attended 2 sessions, I'm only going to appear in the 2 bucket, NOT in both 1 and 2.
Getting to this point is easy by just creating a basic visualization and using COUNT():
student_id | count of session_id 20611 | 4 12345 | 45 999 | 1 89724 | 24
But the outcome I really need is to create something like this, where I'm using a measure to count the number of distinct students who attended a discrete number of sessions:
# of sessions | # of distinct students 1 | 454 2 | 761 3 | 134 4 | 99 5 | 34 6 or more | 28
I can do this in PowerQuery by just creating a table and doing some data transformations, but then the result is static. What I really need is a measure so that, as my users are slicing the data in the report, this table is updating with the number of distinct students. I've browsed the forums and could never find a topic that was directly on-point with what I'm trying to do, and I've tried a few different DAX methods like creating a measure using COUNTROWS('dataset'), and then using CALCULATE(), but nothing works so far. Any thoughts?
Hi pdbenbow,
Create a new table using DAX with the following:
Table =
SUMMARIZE (
ALL ( Sessions[SessionID]; Sessions[Student_ID] );
Sessions[Student_ID];
"Session total"; COUNT ( Sessions[SessionID] )
)This table will give you the count of sessions per Student this will be dinamic based on the upload of the data for each time it's refresh.
You can then Add the Session Total to a table visual and the student ID and do a distinct count this will give you the number of session and number of unique students
You can also link the Studend ID between both columns and make filters and slicers based on this.
Regards,
MFelix
Thanks! This was incredibly helpful -- not exactly what I needed, but it helped me achieve the end result. Here's what I did:
As you suggested, I created a summary table:
tblSummary = SUMMARIZE( ALL(vwTutorClientsSessions[session_id], vwTutorClientsSessions[student_id]),vwTutorClientsSessions[student_id],"Session Total", COUNT(vwTutorClientsSessions[session_id]))
And then I created a grouping using the Session Total column to create the buckets I needed of 1, 2, 3, 4, 5, and 6 or more.
And finally I connected it in my data model using student_id:
vwTutorClientsSessions *--------<--------1 tblSummary
However, this was still giving me a table of static numbers that would not update when I played with the filters and slicers I had in the report already. What I really needed was a measure. So using what you gave me, I just added a measure to tblSummary and pointed it back to my main dataset:
tblSummary[Measure] = CALCULATE(DISTINCTCOUNT(vwTutorClientsSessions[student_id]))
And this gave me the result I needed. Many thanks for the assistance!
Pete
3 Replies
Hi pdbenbow,
Create a new table using DAX with the following:
Table =
SUMMARIZE (
ALL ( Sessions[SessionID]; Sessions[Student_ID] );
Sessions[Student_ID];
"Session total"; COUNT ( Sessions[SessionID] )
)This table will give you the count of sessions per Student this will be dinamic based on the upload of the data for each time it's refresh.
You can then Add the Session Total to a table visual and the student ID and do a distinct count this will give you the number of session and number of unique students
You can also link the Studend ID between both columns and make filters and slicers based on this.
Regards,
MFelix
Thanks! This was incredibly helpful -- not exactly what I needed, but it helped me achieve the end result. Here's what I did:
As you suggested, I created a summary table:
tblSummary = SUMMARIZE( ALL(vwTutorClientsSessions[session_id], vwTutorClientsSessions[student_id]),vwTutorClientsSessions[student_id],"Session Total", COUNT(vwTutorClientsSessions[session_id]))
And then I created a grouping using the Session Total column to create the buckets I needed of 1, 2, 3, 4, 5, and 6 or more.
And finally I connected it in my data model using student_id:
vwTutorClientsSessions *--------<--------1 tblSummary
However, this was still giving me a table of static numbers that would not update when I played with the filters and slicers I had in the report already. What I really needed was a measure. So using what you gave me, I just added a measure to tblSummary and pointed it back to my main dataset:
tblSummary[Measure] = CALCULATE(DISTINCTCOUNT(vwTutorClientsSessions[student_id]))
And this gave me the result I needed. Many thanks for the assistance!
Pete- Hi pdbenbow,
Glad I could point you in the rught direction as you can assume is difficult to give a full working answer without having the full extend of your model and knowing were and how you are filtering and slicing. In my tests I used the relationships between tables to make slicers work.
Glad you pick uo my pointers and improve it in line with your needs.
Regards,
MFelix