Forum Discussion
Power BI Matrix Sort Rows By Value
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
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.
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
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.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
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