Sort column with repeated values by another column

Question

In Power BI Desktop, I'm trying to order the following column with repeated values by an ID column (contains primary key).

This returns the error: "There can't be more than one value in "Nível2"...."

In this other post it seems the suggestion is to concatenate the values of the column so they don't get duplicate.

But I want them to be repeated so they can aggregate values in visuals.

So, what's the workaround for this situation?

Thanks in advance for helping!

Answer 1

The issue is that your sort column (i.e. your ID column) contains multiple values for each value in the column you are trying to sort (i.e. your Nivel2 column).

You need to ensure that your sort column contains only one distinct value for each value in the column you are trying to sort.

One way to achieve this would be to create a new (calculated) sort column based on your ID column. It could be defined like this:

SortColumn:=CALCULATE(MAX('YourTable'[ID]),ALLEXCEPT('YourTable','YourTable'[Nivel2]))

Here is an example of how the SortColumn would behave:

Id  Nivel2              SortColumn
1   Caixa               4
2   Caixa               4
3   Caixa               4
4   Caixa               4
5   Depósitos à ordem   7
6   Depósitos à ordem   7
7   Depósitos à ordem   7

You can now sort Nivel2 by SortColumn.

EDIT - The implementation of the SortColumn should be done in the data source

There seems to be a limitation in PowerBI where it checks the implementation of the sort column rather than the data in the sort column. Therefore the above solution does not work, even though the data in the sort column is perfectly valid. The above solution will throw this error when you attempt to sort [Nivel2] by SortColumn:

This column can't be sorted by a column that is already sorted, directly or indirectly, by this column.

The implementation of the SortColumn should be moved to the data source instead. I.e. if your data source is an Excel sheet, then the SortColumn should be created inside the Excel sheet.

The above answer does explain the issue and the resolvation correctly. The only change is that the SortColumn must be implemented outside of the tabular model (PowerBI) to ensure that PowerBI does not know about the dependency between the SortColumn and the [Nivel2] column.

Answer 2

In my case, I calculate the levels from a parent-child hierarchy Path = Path([id],[father])

For each level: Level1 = LOOKUPVALUE([Name],[id], PathItem([Path],1)) Level2 = LOOKUPVALUE([Name],[id], PathItem([Path],2)) .....

Then I created a new column for each level to sort the column Level: SortL1 = LOOKUPVALUE([nID],[id], PathItem([Path],1)) SortL2 = LOOKUPVALUE([nID],[id], PathItem([Path],2)) .....

id and nID is the same numeric variable but "id" in string format because Path do not support numeric values.