Forum Discussion
DAX Pattern to Sort Non-Unique Names
Thank you danextian
Hi, WishAskedSooner
The only sorting column is indeed what Power BI currently requires. The solution you shared is very useful.
You can reply here and mark it as a solution so that others in the community can quickly find the solution when they encounter similar needs.
Thank you again for sharing, your solution is really great, and it will promote the development of the community together with us.Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Nice work!
The GUI doesn't allow more than one sort value for every distinct value in a column being sorted but, alternatively, you can also force such behavior by using use the Sort by Column property in Tabular Editor.
Below is a sample DAX query returned by performance analyzer when a column custom sorted is added to a viz. The Index/Sort column isn't visible in the viz but is added to the DAX query as well.
Hi danextian,
Thank you for your reply! I am not familiar with Tabular Editor unfortunately. In fact, I barely know my way around in DAX and the DAX Query View.
But, you are right. The problem with sorting one column by another is the cardinality/granularity must match, and there are of two approaches:
1. Update the RowName column to match the granularity of SortID
2. Update SortID to match the granularity of RowName
The first one is the pattern I outlined. The second one involves using a sort column with non-unique values which is possible as long as the sort column has matching non-unique values, but they must match precisely. For example, in the table below, RowName can be sorted on SortID because their granularity match:
However, there is a weird bug in PowerBI which can confuse developers. If you tried to sort on RowName previously and got the error, updating the granularity of SortID, and trying to reapply the sort will still generate the error. This is misleading because if you create a brand new table of the above with the matching granularity, you won't get an error.
Finally, I know that the pattern I outlined can also be executed in PowerQuery.
So ultimately, there are many options and which one to deploy depends on one's requirements.
Tabular editor is an one of the external tools for Power BI. There are properties in there that are not found in the GUI including why when creating a numeric parameter, you get to have a slider slicer that allows just a single value. Most, it not all, of these tools can be downloaded using the Business Ops app. https://community.fabric.microsoft.com/t5/Community-Blog/Business-Ops-The-largest-collection-of-Power-BI-External-Tools/ba-p/1725604