Matrix doesn't sort column alphabetically correct

[L_K_]

Hello,

I have a problem with the alphabetical sort in my matrix.

If I sort my surname column alphabetically it messes up when it comes to distinguishing between “C“ and “Č“, “S“ and “Š“, “Z“ and “Ž“.

Example:

RUT...

SCH...

ŠIR...

ŠKO...

SKR...

SOV...

STA...

ŠTE...

STR...

TIS...

As you can see it dismisses the fact that the “S“ and “Š“ are different letters and treats them both as “S“. It then sorts the surnames based on the second letter, even though the “S” surnames should come before “Š” surnames.

Does anyone know if there is a fix to this?

Kind regards,

L

[L_K_ · Accepted solution]

If anyone else encounters the same or similar problem as me, creating a sorting table helped resolve my problem, since everything else I tried failed.

I made a table containing the first three letters of the alphabet in all variations in the first column and then inserted the row numbers in the second column.

ABC_column	ABC_sort
AAA	1
AAB	2
AAC	3
AAČ	4
AAD	5
...	...

After that I made a custom column in my surname table with the DAX function LEFT (Column = LEFT(Table[surname],3) and made a one-to-many relationship between these two tables. Then I added a related/lookupvalue column so I could sort the surname column based on the sorting column. When I got a circulated dependency warning I just duplicated the surname column and made a work around. Now it seems to work.

After I did this, I saw that my table wasn’t updating correctly. The database was fine, the data import was fine, the data looked fine in the query editor and was refreshed, but when I clicked “close & apply” it said that the data was loaded correctly, but it wasn’t. I don’t know what caused this and I’m looking into it.

[Anonymous]

Hi @L_K_ ,

Create a new table using DAX:

Table 2 = ADDCOLUMNS(GENERATESERIES(
    UNICODE("A"),
    UNICODE("Z")
    ),"Chara",UNICHAR([Value]))

Then create two new columns using DAX:

Column = VAR a=ADDCOLUMNS(GENERATESERIES(UNICODE("A"),UNICODE("Z")),"Chara",UNICHAR([Value]))
var b=COUNTROWS(FILTER(a,CONTAINSSTRING('Table'[test],[Chara])))
var c=LEN([test])
return IF(b=c,1,0)

Column 2 = IF([Column]=1,RANKX(FILTER('Table',[Column]=1),[test]),RANKX(FILTER('Table',[Column]=0),[test]))

Create a slicer based on "Column".
The final results show:

Best Regards,

Dino Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[L_K_]

This doesn't solve my problem, I need the sort of all surnames to be in the correct order, I don't need to filter them. Thank you though for trying to help.

I made a custom sorting table and that worked.

[Bmejia]

I believe it might have to do with the region your power BI Desktop is setup to.  You can check under options and settings\Options\Region Setting

[L_K_]

I did try this already, but it doesn't work.