How to use the slicer on the column

[V-lianl-msft]

Scenario: 

Some users want to filter and summarize based on column names, however, some difficulties arise due to the fact that dax statements basically operate on the data in the specified column.

For example, there is a report card with only names and test takers' scores for each subject, the school wants to have a report that can be filtered using a slicer for each subject's score, how can this be achieved?

Table Used: 

The following is base table:

Expect output:

Subjects can be used as slicers.

Solution 1

Step 1

Based on the table, Select the "Student ID" and "Student Name" columns, then to unpivot other columns.

Step 2

Apply and create the following visual,we get what we want:

Solution 2

In some cases, unpivot is not very applicable due to the huge amount of data. Also, there are times when Power query does not display h data well, (as shown below, when downloading a pbix file shared by someone else, opening Power Query Editor shows that the file cannot be found) so do we have any other solutions? Calculation groups are a good way to use slicers on multiple columns without the need to use "unpivot".

Step 1

In order to use calculation groups, you need to install Tabular Editor on your computer first, then use it, you can create calculation groups.

Why do we need Tabular Editor?

Many partners ask a question: Isn't this Power BI? Why do we need another software to edit it?

For historical reasons, Power BI Desktop has to juggle a lot of things, including visualization and other issues. It's like a shell that can manipulate the DAX engine. The practical problem is this.

What if the DAX engine is upgraded and the Power BI Desktop shell doesn't have the corresponding part of the upgrade yet?

Then, we may not be able to operate the DAX engine through Power BI Desktop.

Tabular Editor is lightly loaded and it is a quick editor for the definition files of the DAX engine. So, many quick edits and batch modifications to the DAX engine are always done in Tabular Editor to almost manipulate the latest features of the DAX engine.

After installing Tabular Editor, we need to create the corresponding measure according to the column:

Step 2

Click external Tools to open Tabular Editor 3, Then right-click Tables>Create New>Calculation Group, or just use the shortcut Alt+7 to create a new calculation group.

Step 3

choose Calculation Items , right-click then Create >calculation Item，then click the Item and change the name in description, and enter the measure created in expression editor:

Here's what happens after all of them are created:

I have created an item sum, which is used to summarize the sum of all disciplines.

Close and apply.

Step 4

Creating visual will achieve what you want.

Unpovit follows simple steps, but changes the table structure, while computation groups do not have an impact on the table structure. Of course, this case is only one applicable case of calculation group, which itself plays a bigger role in Power BI as a new feature introduced last year. You can understand the basic operation through this simple case, and you can learn it in depth according to your needs.

Conclusion: 

As it stands, there are many different methods, and the difficulty of the approach depends on in which way you want to achieve it.

Hope this article helps everyone with similar questions here. 

Author: Lucien

Reviewer: Kerry Wang & Ula Huang

How to slicer columns based on column names.pbix