Power BI Matrix Sort Rows By Value

[Jared_Leonard]

Hi All,

I have a set of data I am using from surveys that is like so: Every question answered is a row in the table with the followiung attributes, a responseid attribute that is unique for each person who has taken a survey, a questionid and questiontext attribute for each question, and order attribute denoting the order each question appears in, and an answertext attribute denoting the text a user entered for a question. Every particular question in a survey is going to have the same questionid, questiontext, and order attributes.

I am trying to organize this data into a matrix, with responseid defining the columns, questionid defining the rows, and the answertext defining the values where a row and column meet.

However, I would also like the question text to be what is display for each row label along the left, and I would like the rows to appear in order according to their order attribute. How can I implement this?

Thanks

Jared Leonard

[DemoFour]

Hi @Jared_Leonard 

Have you got a dimension table of your questions with a sort order column, as this would help to sort the matrix in to question order. 

In my picture the Question ID is also numerical so I can sort any visual by this patten. 

Hope this helps. 

[Jared_Leonard]

Sorry, I'm still new to Power BI, is that under Model View? And what options did you choose to create those tables? Are they pulled directly from a datasource or create using an existing datasource?

[DemoFour]

Hi @Jared_Leonard , 

Not a probem, that is a picture of my model I built for a survey on theModel tab, I split the flat file in the query editor to create a star schema - or data model. 

Check out here Microsoft guidance - star-schema for an overview. 

[Jared_Leonard]

So would the Sort Column just be a custom column named "Sort" or similar with an an integer value denoting where a value should appear in the sort order? How would I select that column using the Matrix visual? The only option I can see in the Matrix is the sort using the column headers, which in my case would be alphabetically using the question text.

Also, how are those relations defined in the model you've shown? Power Query Editor > Merge Queries?

Thanks!
Jared Leonard

[DemoFour]

Hi @Jared_Leonard 

In the query editor you can add an index column to the table, then once it is in desktop you can choose your column to sort by this index column,  then when the column is in the matrix, it will be in the right order for you. 

I created my Dimensions tables, by referencing the file with all the data and then selecting the columns that I need in each dimension, removing all other columns. Then add the index column into the table. 

To create the Fact table, I then merge the new dimesion tables with the dimension ID (or Index)  with the Fact table to create a reference Index and remove the question text. This ID is then used to create my joins in Desktop between the Fact table and Dimension table to create a star shape (you can line these up in different ways if you want e.g. a waterfall with the dimension on top and the fact table below) and the dimensional model.

 
Then you have will have many Questions in the fact table with the index number, but only one of each question in the dimension that corresponds to this. 

When you load the tables either the relationships will join themselves (depending on your file settings) or you can then drag and drop the columns for each table to crate the 1 to Many join.


Is this starting to make sense? 

Kind regards. 

[Jared_Leonard]

Its making more sense, but I'm still seeking clarification to a few things, your continued answers are much appreciated.

In your diagram, the relationship direction goes from the dimension table to the fact table, while the opposite seems to be defined in the reference you linked. Is this detail relevent or is the direction of the relationship arbitrary?

Currently, when trying to define define the matrix, I receive the following:

Is there anything in my model which would suggest a reason for this?

I tried to get it as close to a star schema as I could.

Thanks!
Jared Leonard

[DemoFour]

Hi @Jared_Leonard 

By the looks of your picture, you have not got the Fact table (Responses) in the centre, and you have a Bi directional relationship, as well as other tables that are not filtering into the Fact table because of the relationship, I would guess this is the source of your error in the Matrix. 

What you are aiming for here, is to have the many on the fact table side and the One on the dimensions, with all arrows pointing to the Fact table. If you have parent and child dimensions linked into the fact table, they also need to flow into the Fact table with the many to one in the right directions. 

Have you pivoted your questionnaire answers in your fact table so that the table is thin and long? 

Have you set up keys between the questions and other tables to link to the answers in the Fact table? 

If you can post a copy or a set of dummy data modeled on your PBIX file, it maybe easier to look at if you are stuggling to understand what I have suggested. 

Happy to help you out and contribute to helping others on the forum. 

Kind regards. 

[Jared_Leonard]

Thanks for you response, I've tweaked the structure of my tables according to your comments, they look like this now:

The data goes into the matrix properly now, but I am still confused on the exact steps I would take to sort the rows of the matrix. I'm able to sort the entries of the Fact table but not once the values are brought into the matrix. Would the "Rows" of the matrix need to have the question name and all of the values I am sorting on?

Also if the table is still incorrect, I can provide sample data, I am just unsure how to export said data in a way that's usable to you.

Thanks,

Jared Leonard

[DemoFour · Accepted solution]

Hi @Jared_Leonard 

Glad to hear that you making progress. I have enclosed a picture of a test data and how to add an index column column in the query editor, this can then be sorted in Desktop. 

You can add the same to your dimesions Questions and then when you are in the desk top view sort the column by that row. 

The code you need is 

= Table.AddIndexColumn(#"YOUR PREVIOUS STEP", "COLUMN NAME", 1, 1, Int64.Type)

The column in the UI will add your index

Then when in desk top choose the table column and then the sort by option and choose the index, this will then sort your matrix in the right ordser for you. 

Chose the column you want to sort and then sort by 

If you need to provide dummy data, then this can be pasted in here or you can email it across if that is easier for you. 

Hope this helps. 

kind regards

[Jared_Leonard]

Hi again @demo , and thank you for your invaluable continued help. I have added an index column per your suggestion, and it seems to reflect the intended sort order I had in mind, and when applied to the Matrix the data is sorted properly!

I've ran into a couple of resultant issues, but I think those would belong in a new thread. Thank you again for your help!

[DemoFour]

Hi, @Jared_Leonard 

Good to hear you have resolved your question, more than happy to help and suggest ways to fix. 

Good luck in resolving your other issues. 

please feel free to leave kudos as the solution was accepted. 

Kind regards