Add/Remove Columns of Matrix Visual Using Slicer

[oliverblane]

I want to be able to add and remove columns from a matrix visual using a slicer, very similar to how it is done in the solution of this question: https://community.powerbi.com/t5/Desktop/Adding-Removing-matrix-columns-based-on-Slicer-Filter/m-p/2...

However, my problem is that I have multiple column hierarchies for my problem. In the context of the question I attached, an example would be having Year and Month above the columns Europe/Specific/etc... (in the solution of that question). Their solution does not seem to work for this scenario. Is there a way to do this?

Edit

Sample report: https://meganexuslimited-my.sharepoint.com/:u:/g/personal/oliver_blane_meganexus_com/EUrLLCs7Up1Gn19...

The aim is to be able to select which of the Allocated/Attended/Planned Capacity columns to show in the matrix using a slicer.

Thanks!

[csingleton2]

Here is my Cross Apply SQL Query to unpivot the columns. That way you can have the your base data and the columns that you want to turn on/off in the unpivot. 

SELECT 

       tbl.lngId

      ,tbl.[Student Id]

      ,tbl.[Student Name]

  ,tbl.Grade

  ,tbl.Counselor

  ,tbl.Enrolled

-- ,tbl.Code

-- ,tbl.Cal

  ,tbl.Birthdate  -- DataType: Date

-- ,tbl.Gender

  ,tbl.[Site]

-- ,tbl.[Date]

-- ,tbl.[Description]

-- ,tbl.Comments

  /* These are the unpivoted columns:

  ,tbl.[Period]

  ,tbl.[Semester]

   ,tbl.[Subject]

   ,tbl.strSection

  ,tbl.Title

  ,tbl.Teacher

  ,tbl.Assignment

  ,tbl.Portal

  ,tbl.Points

  ,tbl.[Spcl-Mark]

  ,tbl.[Eff Score]

  ,tbl.Possible

  ,tbl.Grades

  ,tbl.[Class Avg]

  */

-- ,tbl.CreatedDate  -- DataType: DateTime2

-- ,tbl.LastRefreshed -- DataType: DateTime2

  ,UnpivotedData.Attribute 

  ,UnpivotedData.Value 

FROM [dbo].[tblMaterializedStudentGradebookSummary] tbl

/*Note: Cross Apply Values (Attribute, Value) for output.*/

CROSS APPLY (VALUES

      ('Period', CAST(tbl.[Period] AS VARCHAR))

  ,('Semester', tbl.Semester)

      ,('Subject', tbl.[Subject])

      ,('strSection', tbl.[strSection])

  ,('Title',tbl.Title)

  ,('Teacher',tbl.Teacher)

  ,('Date', CAST(tbl.[Date] AS VARCHAR))

  ,('Assignment', tbl.Assignment)

  ,('Portal',tbl.Portal)

  ,('Points', CAST(tbl.Points AS VARCHAR))

  ,('Spcl-Mark',tbl.[Spcl-Mark])

  ,('Eff Score', tbl.[Eff Score])

  ,('Possible', tbl.Possible)

  ,('Comments', tbl.Comments)

  ,('Grades', tbl.Grades)

  ,('Class Avg', tbl.[Class Avg])

  ,('Description', tbl.[Description])

  ,('Created Date',CAST(tbl.CreatedDate AS VARCHAR))

  ,('Last Refreshed',CAST(tbl.LastRefreshed AS VARCHAR))

  ,('Enrolled',CAST(tbl.Enrolled AS VARCHAR))

  ,('Code', tbl.Code)

  ,('Cal',tbl.Cal)

-- ,('attribute', tbl.Birthdate)  -- DataType: Date

  ,('Gender', tbl.Gender)

  ,('Site',tbl.[Site])

  --,(attribute)

-- ,(UnpivotedData.Value)

) AS UnpivotedData(  -- Transform Columns to Rows. 

  Attribute

  , Value

  );

[MFelix]

Hi @oliverblane ,

Without knowing the details is difficult to pin point the correct way, but taking into account that you are refering you have several columns hierarchies, I would create a disconnected table with those hierarchies and then create a switch measure to use on the values.

When you refer months and years on the hierarchie are you refering to 2020, 2021, 2022 and Jan, Feb, ..., Dec or is it on a different format.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[oliverblane]

Hi @MFelix, thank you very much for your reply.

Here is a link to a simplified version of my report with anonymous data: https://meganexuslimited-my.sharepoint.com/:u:/g/personal/oliver_blane_meganexus_com/EUrLLCs7Up1Gn19...

Ideally I would like a slicer that can allow users to select which of the Allocated/Attended/Planned Capacity columns they would like to see in the matrix. For example they might only wish to look at Allocated, or Allocated with Attended, or all three at once perhaps.

Do you know if this is possible? I appreciate your help!

[MFelix · Accepted solution]

Hi @oliverblane ,

For this you need to create a disconnected table with the following format:

Now add the following measure:

Selected Measure value = 
            SWITCH( SELECTEDVALUE(Matrix_Selection[Measure]),
            "Allocated" , SUM(Measure_Selection[Allocated]),
            "Attended", SUM(Measure_Selection[Attended]),
            "Planned Capacity", SUM(Measure_Selection[Planned Capacity])
              )

Add the Measure column from the previous table on the column below the Month, and the measure above on the values.

Result below and in attach PBIX file.

Has you can see the matrix on the bottom only show selected measures.

[Soups]

@MFelixI'm trying to do something similar and essentially wondering if there is a way for the result in a switch line to contain multiple items. Ex. note how the Attended line in the switch function below contains AND (I know this doesn't work, but it's essentially what I'm going for).

Selected Measure value = 
            SWITCH( SELECTEDVALUE(Matrix_Selection[Measure]),
            "Allocated" , SUM(Measure_Selection[Allocated]),
            "Attended", AND(SUM(Measure_Selection[Attended]), SUM(Measure_Selection[OtherRange]),
            "Planned Capacity", SUM(Measure_Selection[Planned Capacity])
              )

In my case, if Allocated is selected I want to show that, but if Attended is selected I want to show multiple columns.

[oliverblane]

Hi @MFelix ,

Thanks again for your reply. Visually, this is exactly what I wanted, however when exporting the data, the Allocated/Attended/Planned Capacity columns collapse into a "Measure" column whereas ideally they would remain as separate columns in the exported CSV file just like how they do for the top matrix in the file you shared (I do not mind this happening for the Contract YR and Month columns).

Is it possible for those 3 columns to remain separate in the exported CSV?

Thank you so much for your help.

[MFelix]

Hi @oliverblane ,

Since this a single measure when you export the values for CSV it will appear in a single column and the measures names will be presented on a different column.

Do you need to export to CSV? There is a new option to export the table/matrix in the exact view you have on the service.

[oliverblane]

I am glad you pointed that out - I did not realise there was that option in the service. Thank you very much for your help!

[MFelix]

See the link:

https://powerbi.microsoft.com/en-us/blog/export-to-excel-improvements-for-table-and-matrix-visuals-p...