Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
kenmuir71
Helper I
Helper I

Switch between two columns in a matrix or table visual

Hi

 

I have a data set like this;

 

ProductSub ProductQty
AW9
AX6
AX10
AZ11
BU5
BV7

 

 

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;

 

SelectedQty
A36
B

12

 

or

SelectedQty
W9
X6
X10
Z11
U5
V7

 

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

 

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

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”:

v-robertq-msft_0-1605250532575.png

 

  1. Sort the [Attribute] column:

v-robertq-msft_1-1605250532579.png

 

  1. Rename columns:

屏幕截图 2020-11-13 145617.png

  1. Apply and close, then create a Slicer and Matrix, place columns like this:

屏幕截图 2020-11-13 145628.png

屏幕截图 2020-11-13 145634.png

 

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.

View solution in original post

4 REPLIES 4
kenmuir71
Helper I
Helper I

Hi Robert - thi sworks perfecttly. Thanks!

v-robertq-msft
Community Support
Community Support

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”:

v-robertq-msft_0-1605250532575.png

 

  1. Sort the [Attribute] column:

v-robertq-msft_1-1605250532579.png

 

  1. Rename columns:

屏幕截图 2020-11-13 145617.png

  1. Apply and close, then create a Slicer and Matrix, place columns like this:

屏幕截图 2020-11-13 145628.png

屏幕截图 2020-11-13 145634.png

 

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
Helper I
Helper I

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
Super User
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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.