Am I able to use one Visual to Filter Several Columns at once?

[Anonymous]

Hello,

I'm currently creating a Training Attendance Workbook for my company using Power BI Desktop. Originally, I had one page level filter for each column which only filtered for employees that have a "completed" status for at least one training, which then was reflected in a table visualization that contains their name, role, and location etc. I then realized that being able to filter for both Complete and Incomplete statuses would be a lot more useful as far as scheduling future training. Each of these trainings have their statuses on a separate column in the same table. My question is, is there a way to create a dax calculation or expression that will filter all of these columns with one visualization? More specifically, can I use the Slicer visualization to filter for either "Complete" or "Incomplete" on every single training status column at once? I've attached a screenshot of our table is I didn't articulate my question properly. I'm fairly new to Power BI and working with diverse sets of data, so any help or insight is appreciated! 

 

[v-micsh-msft · Accepted solution]

Hi joshfdpr,

What is your current situation? To answer your questions, based on the data image you shared, I don’t think we could write any DAX expression to filter multiple columns based on single slicer.

Even the values of those columns are the same, this can’t be done under Power BI desktop.

And if we unpivot those columns, just as what ankitpatira suggested, you may get what you would like to achieve.

I will share some detailed steps here, hope it helps:

1. Unpivot column option is available under Query Editor in Power BI, the first step that we need is to open Query Editor, under Power BI desktop home tab -> Edit Queries;
2. Assuming you have the table already imported, after open Query editor, press ctrl and then select all the columns that have the common value type (complete/ incomplete),
3. Under Transform tab, find the unpivot columns(expand Query Editor if you can’t find the unpivot column)option, click it, change the column name to the one you preferred: (Note: Make sure you have only one additional column besides the columns which you are trying to unpivot, if there are two additional columns, there might be duplicated rows created),
4. Then under Query Editor home tab, click close&apply.
5. In Power BI report View, choose the value as the slicer value.

Regards

[ankitpatira]

@Anonymous I think if you pivot your table you may get what you're after. So after pivotting your strucutre will be as below and then you can use slicer visual for Value column for filtering. 

Column,value

7Stand,complete

7Stand,incomplete

Buying,complete

Buying,incomplete

Change,complete

Change,incomplete