Adding rows to a table

[VietNor]

Hello,

I have a data table with 3 columns: Year, Class, Student wich I get from Query,  and a Dim_school  table

School	Class
School I	Class A
School I	Class B
School I	Class C
School F	Class D
School F	Class E

--

I would like to add the rows with schools and total student pr school per year to the table. The goal is to have a slicer with shows both class and school. I do not want 2 slicer ( school and class) 

Is there a solution for this?  

Thank you!

[v-ssriganesh]

Hi @VietNor,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @VietNor,

May I ask if you have resolved this issue? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @VietNor,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @rohit1991, @danextian, @DataNinja777  for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? If so, please mark it as the solution. This will help other community members solve similar problems faster.

Thank you.

[rohit1991]

Hi @VietNor ,
Yes, there is a solution for this using table union and proper data modeling. Since you want to have a single slicer that includes both Classes and Schools, and display total students per School per Year alongside Class-level data, you can achieve this by appending additional rows to your main fact table (Year, Class, Student) using Power Query.

You can aggregate the total number of students per School per Year by joining the fact table with the Dim_School table (which maps Classes to Schools), then group by Year and School, summing the students. After that, create a new column called "Class" where you replace Class with the corresponding School name for these new rows. Finally, append these aggregated rows to your original fact table. 

This unified table will allow you to use a single slicer (based on the "Class" column) to filter both School and Class data. Make sure your slicer is pulling from a distinct list of values from this column, and you're good to go!

[danextian · Accepted solution]

Hi @VietNor 

You can duplicate the query, rename School to Class and Append the duplicate to the original. This approach will add an extra overhead in the ETL process so this can cause refresh to take longer dependin on the data size.

You can also create a separate dimensions table with a single column for both class and school but since both are not in the same column in the fact table, you must use a disconnected table approach and filter the visual indirectly using measures.

Please see the attached sample pbix.

[DataNinja777]

Hi @VietNor ,

To achieve your goal of having a single slicer showing both classes and schools, and to include rows with total students per school per year in your table, you can start by creating a unified dimension table that includes both class names and school names. This table can be named Dim_Combined, and it should contain one column, for example, [Slicer], with values like "Class A", "Class B", ..., "School I", "School F".

Next, in Power Query, load your original student data table which includes the columns Year, Class, and Student, and also load the Dim_School table that maps schools to their respective classes. You'll need to create a new query that merges the student table with the school mapping table. To do this, perform a left join from the student table to the Dim_School table using the Class column. This will give you a new column containing the school each class belongs to. Then, remove all rows where the school column is null, and group the resulting table by both Year and School, aggregating the sum of students.

Here's a simplified M code snippet for the transformation:

let
    Source = StudentData,
    SchoolMap = Dim_School,
    Merged = Table.NestedJoin(Source, {"Class"}, SchoolMap, {"Class"}, "SchoolTable", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "SchoolTable", {"School"}),
    Filtered = Table.SelectRows(Expanded, each ([School] <> null)),
    Grouped = Table.Group(Filtered, {"Year", "School"}, {{"Student", each List.Sum([Student]), type number}}),
    Renamed = Table.RenameColumns(Grouped, {{"School", "Class"}})
in
    Renamed

This final table has the school names in the same column as the classes, renamed to Class for compatibility. Now, go to your original student table and append this new table of school totals to it, so that your unified fact table includes both class-level and school-level data.

Then, in the data model, link the [Class] column from this unified fact table to the [Slicer] column in the Dim_Combined table. Now, using a single slicer from Dim_Combined, you can slice your visuals by either class or school, and your measures such as:

Total Students = SUM(Fact_Students[Student])

will respond appropriately. This approach avoids having two slicers and integrates school totals seamlessly into your report.

Best regards,