Forum Discussion
Multiple Fields on Record and Filtering
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 !!
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.
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).
2 Replies
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.
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).