Forum Discussion
Custom sort column matrix table
Hi everyone,
I've created a visual using the Matrix visual in Power BI.
However, I need help with custom sorting. Specifically, I want to display "Overall DQ" first, followed by the other categories.
Could someone guide me on how to achieve this?
Thanks in advance!
Hi, Anonymous
Based on your information, I create a sample table:
Then create a new table, give it a fixed sort:
Table 2 = SUMMARIZE('Table','Table'[Category],"_Sort",SWITCH( 'Table'[Category], "Overall DQ", 1, "Accuracy", 2, "Completeness", 3, "Timeliness", 4, "Uniqueness", 5, "Validity", 6, 99 ))Create relationship, select the "Category" column, click "Sort by Column", and select the "_Sort" column
Add "Data Product Project Name" to the rows of the matrix, "Category" to the columns of the matrix in "Table 2", and "Score" to the values of the matrix, here is my preview:
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi, Anonymous
Based on your information, I create a sample table:
Then create a new table, give it a fixed sort:
Table 2 = SUMMARIZE('Table','Table'[Category],"_Sort",SWITCH( 'Table'[Category], "Overall DQ", 1, "Accuracy", 2, "Completeness", 3, "Timeliness", 4, "Uniqueness", 5, "Validity", 6, 99 ))Create relationship, select the "Category" column, click "Sort by Column", and select the "_Sort" column
Add "Data Product Project Name" to the rows of the matrix, "Category" to the columns of the matrix in "Table 2", and "Score" to the values of the matrix, here is my preview:
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi! Does this approach work only with calculated table?
I have the same categories in 2 tables, so I just created this sorting table from the scratch and uploaded it to PowerBI - but seems such approach is not workingHi, AnLe
This approach works well for calculated tables because they are dynamically generated in the data model in Power BI and can be used directly in the model. If you're trying to create a sort table from scratch and upload it to Power BI, this approach might not work, because the uploaded table doesn't automatically establish relationships with existing tables, and sort logic isn't automatically applied.
If you want to use the uploaded table for custom sorting, make sure that the uploaded table has the correct relationship with the existing data table. Relationships are usually established through a common column, such as Category. The table you upload at the same time needs to contain a column for sorting, such as _Sort, and you'll need to manually set the sort order in Power BI (via the Sort by Column feature). Make sure that the categories in the uploaded table are exactly the same as those in the existing table, otherwise the sorting may not be applied correctly.
If you already have the same category in two tables, it is recommended that you use the method of calculating tables instead of uploading tables. Calculating tables ensures that the sorting logic is consistent with other tables in the data model. Especially when you need to dynamically generate sorting logic in your Power BI data model. If you want to use the uploaded table for sorting, you need to make sure that the relationship is set correctly and set the sort order manually. It is recommended that you continue with the method of calculating the table as it is more flexible and easy to maintain.Best Regards
Yongkang Hua
Hi Anonymous
Matrix column categories are sorted alphabetically in ascending order or by using custom column sort. The underlying column must be custom sorted by another column. Please read this blog: https://radacad.com/sort-a-column-with-a-custom-order-in-power-bi
