keep the 2nd row field of a matrix sorted (by an index) when I order the 1st row by column value

[CR7]

I have a matrix that has 2 fields in a row. The 1st is the cost center and the second is the income statement, which must have the items ordered in a certain way, such as the following:

When I order the 1st field of the matrix rows by value order, the 2nd field of the rows loses the order, as follows:

thanks for a solution

Carlos Reis

[CR7]

To resolve the situation in a first option I opted for a solution with two matrices side by side. The first one shows the cost centers ordered and when I choose one of the cost centers the second matrix shows the DR as it should be, as follows:

[MFelix]

Hi @CR7 ,

When you refer that you want to have the sorting done by the values order do you mean that you want to have it sorting by the 419 € is that it?

When you sort the visualzations (any visualization) by values then the axis or rows headers get resorted by the specific value you select in this case since you are making a sort by the values of the matrix the default sorting of the columns will be "overwritten". Has you can see the values for level two are also in a decreasing order.

[CR7]

Dear Miguel,

What you say is correct. But I think it is possible via DAX to force the 2nd field (DRE lines - field [Nivel 1]) to be sorted according to "Index 1" (which allows the lines to be sorted in the correct way). The 2nd field [Nível 1] is defined in PBI to sort by [Index 1] and thus be able to keep the lines in the correct order. I think that to avoid the default behavior of the matrix you have to "force" it with DAX.

[CR7]

HOW IS IT POSSIBLE THAT THIS IS AN EASY SOLUTION IN EXCEL AND HAS NO SOLUTION IN POWER BI? IT SHOULD BE SEEN URGENTLY BY THOSE RESPONSIBLE FOR PBI UPDATES

[CR7]

See this example by 

 v-cherch-msft

Employee

we have the following table called "Table3"

we can create a matrix in which the 1st row field "Items" is ordered by value and the 2nd row field "Month" orders by Month, all this with the measure [Measure] shown below

Measure =

var e = SELECTEDVALUE ( Table3 [MonthNo] )

var c = CALCULATE (

                            SUM ( Table3 [Numbers] ) ,

                            ALL(Table3[MonthNo])

                            )

var t = SUMMARIZE ( ALLSELECTED ( Table3 ) ,

                    Table3 [Items] ,

                    Table3 [MonthNo] )

return

COUNTROWS (

                FILTER ( t , ISONORAFTER ( [TotalNumbers] , c , ASC, Table3 [MonthNo] , e , DESC  ) )

            )

I think it will be a solution of this type

[CR7]

the previous example can be found at:

https://community.fabric.microsoft.com/t5/Desktop/Sorting-a-matrix-second-level-sort-by-column-not-w...

[MFelix]

Hi @CR7 ,

You are correct that this does the trick and if you check my previous answer you see that I refer that you can achieve it by adding another column to your table to do the sorting of the matrix.

Be aware that if a user clicks any other column on your visualization you will get the same error, you cannot have a default behaviour that you click on any column on a visual and the row headers get with a different order at different levels all of the levels will get order accordingly to the column selected.

In the case you refer that order is not the Numbers but the Measure value.

But if you go for that approach I adivise you to do two additional workarounds:

• Hide the column that you are using has a ranking
• Add a shape on top of the column header so that the user is not abble to change the sorting of the columns

Be carefull with this approach also because if the users do an export to CSV or Excel they will see that column that is hidden.

If you need help adjusting the DAX code for your needs please let me know.

[MFelix]

Hi @CR7,

In a visual sorting you cannot force it trough dax since this would need to have another column on your table to do the sorting by that rank, or change the result to have some additional white characters to get the sorting correct. 

DAX is based on context and in this case your context is the sorting ascending and not the column that you are using at 2nd level.