Create a hierarchy between tables following relationship

Question

I am confused with pivot tables, and it's a recurring issue! Say I have put 2 tables in my data model (classes and students):

tables classes and students

My model is this one:

enter image description here

I want to build this simple pivot table, displaying under classname, student name (no sum or number, just plain values) like so.

expected pivot table

If I do it like that, as proposed by @Cpt.Whale :

enter image description here

I get all students under each class like in a cartesian product. I just want to get the students who are associated with the class. If student fname were in class table, I simply would do a hierarchy. I want to do the equivalent of the hierarchy with the 2 tables. It might be obvious, but I cannot do it!

Update

I tried again and found a way to do it using RELATED() DAX function in a calculated column :

enter image description here

I this case, the solution proposed by @Cpt.Whale indeed works correctly :

enter image description here

Is there a better and simpler way without using a calculated column, directly in Pivot Table, with kind of an implicit RELATED funtion ?

Sorry for all screenshots being in French Excel.

Answer 1

If data is sorted this way, in Office 365 you could also achieve it using formula:

=HSTACK(MAP(classes[classname],LAMBDA(a,IF(COUNTIF(TAKE(classes[classname],1):a,a)-1,"",a))),
        XLOOKUP(Students[studentid],students[studentid],students[fname]))

Where you could replace TAKE(classes[classname],1) with the first cell ID, like A3 or if you start in the same row as the table with classes[@classname]