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
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?
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.
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 LeonardHi 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