Multiple Fields on Record and Filtering

[luvagoldenk9]

Hello,

I recently took over a dashboard where the source data has 11 individual project code fields with names A1, A2, B1, B2, B3, C1, D1, D2, E1, F1, F2 (each field exists on each record). The field values on each record are 0 or 1 representing if the person worked a particular code on a project. 

Right now each of these 11 project code fields are in the Filters pane.

The user does not like having all these 11 project code fields in the Filters pane. They requested to group all the codes into a Parent (say 'Project Code') and then be able to filter on that single field 'Project Code' where it would then open up for the user to select which of the individual fields (A1, A2, etc.) to filter on.

I've tried using a hierarchy but that doesn't work as each of the individual project codes are on the same level and do not fall into a hierarchy per se. A separate product code table didn't work well either as there can only be one effective key relationship.

I have to be missing something. Any thoughts/suggestions on how to implement this request? Thank you !!

[AlexisOlson · Accepted solution]

You essentially want to unpivot your data.

Instead of

Person	A1	B1	C1
Jim	1	0	1
Bob	0	1	1

you want

Person	Project	Status
Jim	A1	1
Jim	B1	0
Jim	C1	1
Bob	A1	0
Bob	B1	1
Bob	C1	1

This kind of transformation is simple in the query editor but more painful in DAX.

[ntaylo06 · Accepted solution]

The Filter Pane isn't very customizable, so if you are looking for a different kind of interaction you will  have to make it yourself.

If it were me I would separate the A1, A2, etc. columns into a separate table and then unpivot them. This would allow you to list each of these columns that has a value (just by filtering out 0 values).

Go from this:

Project Name	A1	A2	B1
Proj1	1	0	0
Proj2	0	1	1

to this:

Project Name	Code	Value
Proj1	A1	1
Proj1	A2	0
Proj1	B1	0
Proj2	A1	0
Proj2	A2	1
Proj2	B1	1

This would allow users to filter by Code and then Value (or you could only ever show Value=1).

https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f5882...

[AlexisOlson · Accepted solution]

You essentially want to unpivot your data.

Instead of

Person	A1	B1	C1
Jim	1	0	1
Bob	0	1	1

you want

Person	Project	Status
Jim	A1	1
Jim	B1	0
Jim	C1	1
Bob	A1	0
Bob	B1	1
Bob	C1	1

This kind of transformation is simple in the query editor but more painful in DAX.