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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Lannie84
New Member

How to stop my pie chart from hiding columns in my matrix when a pie slice is selected

I have a large 50k+ row data set for a survey that is run annually. I have some of the data displayed in a matrix similar to this:

 

LocationYearVariable 1Variable 2Variable 3Variable 4Variable 5
One2020aabaccdaea
Two2020aabbccdbed
Three2020aabdccdbea
Four2020abbdccdcee
Five2020abbccaddec
Six2020acbbcddaee
Seven2020abbdcbdbed
Eight2020acbacbddea
Nine2020aabacadcea
Ten2020acbcccdded

 

I currenty have working slicers to include/exclude different survey years and various location parameters (such as state, country etc.). I want to add a some sort of pie chart or slicer that allows users to filter the matrix by specific variable responses.

 

For example: One user is only interested in the responses to variables 2-5 when variable 1 is "aa".

When I add a pie chart for Variable 1 and select "aa", all the other variable columns (2-5) are removed from my matrix and I get a matrix like this:

 

LocationYearVariable 1
One2020aa
Two2020aa
Three2020aa
Nine2020aa

 

Instead I want the matrix to look like this:

LocationYearVariable 1Variable 2Variable 3Variable 4Variable 5
One2020aabaccdaea
Two2020aabbccdbed
Three2020aabdccdbea
Nine2020aabacadcea

 

The current data I have in the visuals are like this.

Matrix visual:                                                                          Pie chart visual:

Lannie84_0-1674599829801.png   Lannie84_1-1674599959809.png

 

Here is my dummy data:

https://drive.google.com/file/d/1iSj35n-rpWLWL4Raq0KZsbOxqKz14I-n/view?usp=sharing

1 ACCEPTED SOLUTION
olgad
Super User
Super User

Hi, there,
Filtering happens based on rows and the columns created in the visual matrix are actually rows in your data. 
Here is the result, you have to duplicate your table in Power Query, select variable column and pivot it. 
Then create a primary key between two tables using concatinate 

Key = Sheet1[Year]&"-"&Sheet1[Location]&"-"&Sheet1[City]&"-"&Sheet1[State]&"-"&Sheet1[Country] 
Make the Cross filtering : Both. 
Change the visual from Matrix to Table. 

olgad_0-1674603732077.png

Dummy data.pbix


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

View solution in original post

1 REPLY 1
olgad
Super User
Super User

Hi, there,
Filtering happens based on rows and the columns created in the visual matrix are actually rows in your data. 
Here is the result, you have to duplicate your table in Power Query, select variable column and pivot it. 
Then create a primary key between two tables using concatinate 

Key = Sheet1[Year]&"-"&Sheet1[Location]&"-"&Sheet1[City]&"-"&Sheet1[State]&"-"&Sheet1[Country] 
Make the Cross filtering : Both. 
Change the visual from Matrix to Table. 

olgad_0-1674603732077.png

Dummy data.pbix


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.