Summary table with slicer support

[Damondo]

Hi All, 

I'm hoping someone will be able to help the following. In a simplified form, have a table which lists a number of StudentID's alongside several columns stating which group the ID is a member of.

StudentID	Group1			Group2
1		Yes			True	
2		Yes			False
3		No			False

As per the example above, the options have been simplified to Yes/No and True/False to demonstrate that it's not standardised options for all groups.

I would like to create a new table which has a relationship with the original:

Group Type		Group Name
Group1			Yes
Group1			No
Group2			True
Group2			False

The relationship comes in as I'd like to place these two fields into a table visual and drop in some measures that rely on links to the original StudentID. The goal is to see how the membership of those groups affects the performance measures. I'll then order the results by the groups with the most significant affect on performance.

I've had a look at GroupBy and SummariseColumns but I can't get it to create the above.

Any help would be appreciated!

Many thanks

[v-cherch-msft]

Hi @Damondo 

You may try to use 'Unpivot columns' as below.Then you may get the table.Attached sample file for your reference.

Regards,

[v-cherch-msft · Accepted solution]

Hi @Damondo 

You may try to use 'Unpivot columns' as below.Then you may get the table.Attached sample file for your reference.

Regards,

[Damondo]

Thanks so much @v-cherch-msft, this is exactly what I need. On my file I've created a copy of the main student table with reduced columns then run an unpivot on there. 120k rows but not a problem for Power BI! One extra thing I had to do with the relationship between the StudentID's was to set the Cross Filter to 'Both'. That way, I could put measures from the student table into the unpivot table visual.