Dynamic Columns in a table from two different datasets

[EpicTriffid]

I'm not sure if this is at all possible but after scratching my head, I figured I would ask here:

What data am I using?:

I have two datasets. One is a list of all units and the scores that students have achieved throughout the year. Off this, I have a measure that calculates the Average Final Unit Score based on what the students have gotten.

My second dataset is entry profile data. This also has student IDs in it that can be linked up to the students in the unit performance table above, but has a list of all the characteristics of that students (Age, Gender, Ethnicity, Domicle, etc.) 

What I want to see:

I want a table that columns can be dynamically added to that will show me a breakdown of scores based on different characteristics. So, I would have a slicer with my unit titles on, then a table with my Average Final Unit Score. This table can then have different characteristics added to it to see how different combinations affect scores.

Below you can see that I would add Ethnicity and Gender, and then remove gender and replace it with First Gen. I could add as many as I like and see all the different combinations.

What I've tried:

I tried unpivoting all other columns except for the student ID in my entry profile data to give me something like below, but it doesnt link up with unit title, despite creating a relationship using the student IDs. 

I've also tried the "DAX Table and Measure" method here: https://www.nathanprats.com/4-different-ways-to-dynamically-change-tables-rows-in-power-bi/ 

This makes a great table of all the characteristics broken down into their constituent parts, but while I can then use it the also what the Characteristic column of a table shows, it doesn't add the extra columns I want for comparisons. 

Conclusion:

Basically what I would like is to mimic the two images above, but make those different columns be added or removed by use of a multi select slicer. Any help or insight would be great.

Thankyou!

[Anonymous]

Hi @EpicTriffid ,

Could you please provide some sample data in your two datasets and your desired result with examples? Please also provide the formula of measure Average Final Unit Mark. The value of measure Average Final Unit Mark will be changed dynamically based on the added columns into visual? If yes, what's the logic?

In addition, you can refer the content in the following links about showing or hiding fields in visual.

Show/Hide column in Power BI Table/Matrix visual

Show / Hide column based on condition or slicer

Best Regards

Rena