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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hawb
Helper I
Helper I

Dynamically adding row & column values to the Matrix/pivot visual with slicers

 

I have a pubilc opinion dataset with responses for about 50 questions.  Additionally I have a variety of demographics (Age, Gender, Education, etc.).   The desired behavior is to dynamically generate different "views" of a matrix table by selecting "Row" and "Column" values from 2 different slicers/filters (One slicer = "Row" source, One slicer = "Column" source). 

 

For example, if I select Q2 as Row and Gender as Column, I would get that matrix output.  Then if I selected Q4 as Row and Education as Column, I would get that matrix output, etc.

 

Capture.PNG

 

I'm half way there and looking for assistance on the other half!  Here is what I have.

 

1.) I took my dataset and I created an "Attribute List" through "Unpivoting" the columns in my data source. This created a datasouce I could use to select the various dataset columns in a Slicer.

 

2.) I created a Matrix Visual and added the "Attribute" from the AttributeList Table as the source for the Row.  This allows me to dynamically change the Row values based on the slicer selection. This works great.

 

This is where I'm stuck.  I would like to do the same thing for the Column, but I cant use the same "Attribute" because then I'm simply getting a Matrix of the same selected value (i.e. Gender x Gender).  It seems there HAS to be a way.

 

Any thoughts?

 

Thanks!

Mike

 

 

5 REPLIES 5
hawb
Helper I
Helper I

 

Older post, still no solution.  Just throwing this back out there to see if there may be any fresh thoughts on how to achieve this.

 

Thanks!

Greg_Deckler
Super User
Super User

Very interesting, any chance you could provide sample data or the PBIX to play with? I'm thinking maybe some variation of the disconnected table trick maybe? https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

 

But I'd have to play with it. I'm thinking of a table that associates your Column to the potential values of the column with a relationship involved. Then you would use the "values" column from this table and it would be filtered via the selection of the slicer to only the values you wanted. Then it would just be a matter of getting the measure calculation correct. But, I'm having trouble visualizing your data to be sure.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks Greg. I'm honored to have a Super User taking a look at this:)

 

So, yes, I thought the same thing regarding some sort of relationship.  Consider my dataset has 2 types of data.

 

1.) Questions (q1, q2, q3, etc.)

2.) Demographics (Age, Education, Gender, etc.)

 

I tried creating 2 datasets (duplicates of each other) thinking I could do what you described.  In the first dataset I unpivoted just the Questions giving me a unique attribute list of questions (q1, q2, q3). These would be my Row Values.  For the 2nd dataset I did the same for just the Demographics.  They would be the source of my Column values (Age, Education, Gender).  I preserved the ID field in each table thinking I could then link them and magic would occur.   Alas, by unpivoting the data, the ID's are no longer unique (i.e. there are now 20 ID's =1.) The result is a Many to Many query that doesnt work.

 

Unfortunately I'm not able to attach a file (?) here, but here is a dropbox link with the *.pbix file.

 

https://www.dropbox.com/s/3byd9eg4ylq5zbq/Dynamic%20Matrix.pbix?dl=0

 

Thanks!

Hi @hawb,

I am afraid I could not figure out what you want, could you please post your desired result if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Daniel.  Does this illustration help?  This functionality would allow me to dynamically explore matrix tables by being able to select different rows AND columns to use in the Matrix visualization.  I can figure out how to dynamically populate one of the two, but not both.

 

Thanks!

 

Capture.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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