Forum Discussion
Dynamically Show/Hide Columns and Rows Based on Single Slicer Selection
I have data that has been collected from Microsoft Forms and has been uploaded to Power BI. With this data, I want to be able to create a dynamic matrix based off one slicer.
With this slicer selection, I want to be able to dynamically show specific columns while also only showing the rows with value selected in the slicer. This table must update live with Forms responses as well.
The reason that some cells have no value is because in the form, specific questions are answered based off question branching.
For example, the users will rate the product. Only certain questions are applicable to certain products.
Product | Efficiency Rating | Accessibility Rating | Visual Appeal Rating | Price Rating | Safety Rating |
A | 5 | 1 |
|
|
|
B |
|
| 2 | 3 | 5 |
C |
|
| 4 | 3 | 2 |
D |
|
| 5 | 5 | 1 |
B |
|
| 1 | 2 | 5 |
A | 1 | 2 |
|
|
|
If Product A is selected in the slicer, the matrix should look like this:
Product | Efficiency Rating | Accessibility Rating |
A | 5 | 1 |
A | 1 | 2 |
If Product B is selected in the slicer, the matrix should look like this:
Product | Visual Appeal Rating | Price Rating | Safety Rating |
B | 2 | 3 | 5 |
B | 1 | 2 | 5 |
If Product C is selected in the slicer, the matrix should look like this:
Product | Visual Appeal Rating | Price Rating | Safety Rating |
C | 4 | 3 | 2 |
And so on…
The closest I have gotten was by using field parameters to show/hide the columns based on the slicer selection but I then lose the ability to filter the correct rows (can use only one slicer!) as options B, C, and D show the same columns. Is there a relationship I need to define? I am having difficulty as the only filter is the product letter but I'm not sure how to filter both rows and columns using just that parameter. How can I associate the product letter slicer with both the rows and columns?
I really need help and as much information as possible. Thank you very much.
Hi jjjtr945 ,
Believe the best option is:
- Add an Index Column
- Select the index and Product and unpivot other columns
- Filter out the blanks
Create your matrix with the following way:
- Rows:
- Index
- Products
- Columns:
- Attribute
- Values
- Value
- Turn off the step layout
- Turn of wrap on all options
- Reduce the size of the index column until is invisible:
2 Replies
Hi jjjtr945
Columns can't be inserted based on conditions.
What you saw with field parameters
There is only one option to choose columns, and they must be chosen manually by the user.If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi jjjtr945 ,
Believe the best option is:
- Add an Index Column
- Select the index and Product and unpivot other columns
- Filter out the blanks
Create your matrix with the following way:
- Rows:
- Index
- Products
- Columns:
- Attribute
- Values
- Value
- Turn off the step layout
- Turn of wrap on all options
- Reduce the size of the index column until is invisible:
