Forum Discussion

hawb's avatar
hawb
Helper I
7 years ago

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 gener...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

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/ba-p/279563

 

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.

  • hawb's avatar
    hawb
    Helper I
    7 years ago

    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!

    • v-danhe-msft's avatar
      v-danhe-msft
      Microsoft Employee
      7 years ago

      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

      • hawb's avatar
        hawb
        Helper I
        7 years ago

        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!