Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Cummunity,
I am currently struggling with a sorting problem in a hierarchy.
I have a master data table that also contains a hierarchy and also contains an order within a hierarchy node.
The hierarchy has actually been solved well. There are enough suggestions here and they all worked wonderfully.
But the sorting worries me.
In the Power Query in the query, I also have the correct order simply because of the iteration and the child index.
But after I insert the hierarchy into the matrix, Power Bi sorts the elements itself. I have tried to define the sorting of the individual elements in the table view. As with the month names sorted by month number. Unfortunately, I can't manage the sorting, as several people can have the same name. Streets can also have the same name.
Is there a way to restrict the sorting so that all columns are sorted by an index column?
Does anyone have any ideas?
Many thanks to you already now
Hi @nevs77 ,
As far as I know, Power BI defaults to having just one separate index number for each row of values, or else when doing a Sort by column, there will be an error of one value having more than one index number, or else it will be sorted alphabetically
You can try changing the data to the following form so that each value is independent and the data is sorted alphabetically to achieve your desired result:
Notice:
Power bi aggregates the same values of columns in Matrix Visual, so you can see that [Group3]=Name_1 in the image below will only show one value:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you v-yangliu-msft very much for your answer.
You have confirmed my problem.
My data is already available as in your second picture, only the ID is still there (and the different columns for sorting attempts). This is clear but unfortunately not suitable for sorting.
This table is only a master data table. In my result table there are values that are linked in the data model via the ID. Classic star schema.
That is why no aggregation by name is allowed. There are also different people who happen to have the same name. They also have different social security numbers and the benefits are not permitted to be aggregated.
It is annoying that the sorting is actually correct in Power Query, but cannot be used in the dashboard. Also that the values are always aggregated and it is not possible to specify that the aggregation should be made according to certain criteria, such as an ID column.
This would perhaps be a case for product development. Are these functions perhaps already in development? Have I overlooked something? A small hack or a function or something else?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
109 | |
89 | |
76 | |
66 |
User | Count |
---|---|
125 | |
111 | |
100 | |
83 | |
71 |