Grouping the intake and identify number of students who did not enroll for other classes among the student in the intake

weizer 0
Viewed 42
intake            class       student_id
Sep 2022 - Eng    English     100
Sep 2022 - Eng    English     101
Nov 2022 - Sc     Science     100
Jan 2023 - Bio    Biology     101
Nov 2022 - Sc     Science     102
Sep 2022 - Eng    English     102
Jan 2023 - Bio    Biology     102
Jan 2023 - Bio    Biology     103
Jan 2023 - Bio    Biology     105
Feb 2023 - Eng    English     104
Feb 2023 - Eng    English     103

Hello everyone,

I have a table as shown above. Each row in the table represent the student who is going to attend the classes. For example by looking at the Sep 2022 English class, I know that students with ID 100,101,102 are going to attend the class, and student 100,102 are going to attend Nov 2022 Science class, etc...

What I want to do is to transform the table into another format where it tells how many students did not attend or are not going to attend other classes among the students that are attending the class right now. The table below is the expected output:

enter image description here

I will show how to get the value in the table that are shown in the screenshot:

For example

When student 100,101,102 are attending the Sep 2022 English class, among three of them:

  • None of them did not attend or not going to attend English class (as they are attending the English class right now);
  • One of them did not attend or not going to attend science class (student 101) since only student 100,102 are in the list of science class;
  • One of them did not attend or not going to attend biology class (student 100) since only student 101,102 are in the list to attend biology class and student 100 is not in the list.

Hence, for Sep 2022 - Eng intake:

  • no_english = 0
  • no_science = 1
  • no_biology = 1

Giving another example

When student 101,102,103,105 are attending the Jan 2023 Biology class, among 4 of them:

  • One of them did not attend or not going to attend English class (student 105) since student 101,102 attended Sep 2022 English class and student 103 going to attend Feb 2023 English class;
  • three of them did not attend or not going to attend science class (student 101,103,105) since only student 102 are in the list of science class;
  • None of them did not attend or not going to attend biology class since all of them are attending Biology class right now.

Hence, for Jan 2023 - Bio intake:

  • no_english = 1
  • no_science = 3
  • no_biology = 0

I have been struggled to transform the data into the desired format like what I show in the screenshot. In fact, I'm not sure whether it is possible to do it or not using powerquery or DAX. Any help or advise will be greatly appreciated. Let me know if my question is not clear.

powerquery group-by dax powerbi
2 Answers

enter image description here

Add 3 measures to a table as follows:

no_science = 

VAR ids = VALUES('Table'[student_id])
VAR ids_sci = CALCULATETABLE(VALUES( 'Table'[student_id]), REMOVEFILTERS('Table'), 'Table'[class] = "Science")


RETURN COUNTX( EXCEPT(ids, ids_sci), 'Table'[student_id])+0


no_english = 

VAR ids = VALUES('Table'[student_id])
VAR ids_eng = CALCULATETABLE(VALUES( 'Table'[student_id]), REMOVEFILTERS('Table'), 'Table'[class] = "English")


RETURN COUNTX( EXCEPT(ids, ids_eng), 'Table'[student_id])+0



no_biology = 

VAR ids = VALUES('Table'[student_id])
VAR ids_bio = CALCULATETABLE(VALUES( 'Table'[student_id]), REMOVEFILTERS('Table'), 'Table'[class] = "Biology")


RETURN COUNTX( EXCEPT(ids, ids_bio), 'Table'[student_id])+0
userjpYe10

For fun, an M version

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"intake", "student_id"}, {{"data", each _, type table }}),
AllCombos =Table.ExpandListColumn( Table.AddColumn(#"Grouped Rows", "class", each List.Distinct(Source[class])), "class"),

T1 = Table.ExpandListColumn(Table.AddColumn(Table.FromList(List.Distinct(Source[class]), null,{"class"} ),"student_id", each List.Distinct(Source[student_id])), "student_id"),
#"Merged Queries0" = Table.NestedJoin(T1, {"class", "student_id"}, Source, {"class", "student_id"}, "Table1", JoinKind.LeftOuter),
StudentNo = Table.AddColumn(#"Merged Queries0", "No", each if Table.RowCount([Table1])=0 then 1 else 0),

#"Merged Queries" = Table.NestedJoin(AllCombos, {"student_id", "class"}, StudentNo, {"student_id", "class"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"No"}, {"No"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table2",{"student_id", "data"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[class]), "class", "No", List.Sum)
in  #"Pivoted Column"
horseyride0
Related