Forum Discussion
Helper ISwitch between two columns in a matrix or table visual
Hi
I have a data set like this;
| Product | Sub Product | Qty |
| A | W | 9 |
| A | X | 6 |
| A | X | 10 |
| A | Z | 11 |
| B | U | 5 |
| B | V | 7 |
I want a my matrix, table visuals and various visualisations to change between columns 'Product' and 'Sub Product' by using a slicer, so outputs would be;
| Selected | Qty |
| A | 36 |
| B | 12 |
or
| Selected | Qty |
| W | 9 |
| X | 6 |
| X | 10 |
| Z | 11 |
| U | 5 |
| V | 7 |
I know you can switch y using a measure looking at an unreletaed table for the slicer, but can't get it to work for a text column? Also I know you could switch your visuals using bookmarks, but this isn't practical given there are dozens of reports which require this functionality. Any help would be appreciated!
Thanks
Ken
Hi, kenmuir71
According to your description, I suggest you simply do this in Power Query editor, you can follow my steps:
- Go to the Power query editor, select [Qty], choose “Unpiovit other columns”:
- Sort the [Attribute] column:
- Rename columns:
- Apply and close, then create a Slicer and Matrix, place columns like this:
And you can get what you want.
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
kenmuir71 , Dynamic M parameter or bookmarks
https://www.youtube.com/watch?v=c1mezIySFf8
Bookmarks https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive
kenmuir71Hi - thanks for your reply, I should have been more specific. A Direct Query solution is not practical, is there a solution in DAX?
I don't want to use bookmarks as you have to change every visual/table in a large set of reports, so its not practical.
Thanks
- v-robertq-msft
Community Support
Hi, kenmuir71
According to your description, I suggest you simply do this in Power Query editor, you can follow my steps:
- Go to the Power query editor, select [Qty], choose “Unpiovit other columns”:
- Sort the [Attribute] column:
- Rename columns:
- Apply and close, then create a Slicer and Matrix, place columns like this:
And you can get what you want.
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- kenmuir71
Hi Robert - thi sworks perfecttly. Thanks!
