Forum Discussion
Solved
Add/Remove Columns of Matrix Visual Using Slicer
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-Remov...
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.
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
);