Switch between two columns in a matrix or table visual

[kenmuir71]

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

[kenmuir71]

Hi Robert - thi sworks perfecttly. Thanks!

[v-robertq-msft · Accepted solution]

Hi, @kenmuir71 

According to your description, I suggest you simply do this in Power Query editor, you can follow my steps:

1. Go to the Power query editor, select [Qty], choose “Unpiovit other columns”:

2. Sort the [Attribute] column:

3. Rename columns:

4. 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 - 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

[amitchandak]

@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