Forum Discussion
Adding rows to a table
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.
Super UserHi 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!