Forum Discussion

L_K_'s avatar
L_K_
Icon for Helper I rankHelper I
2 years ago
Solved

Matrix doesn't sort column alphabetically correct

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...
Need Help
  • L_K_'s avatar
    L_K_
    2 years ago

    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_columnABC_sort
    AAA1

    AAB

    		2
    AAC3

    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's avatar
Anonymous
Not applicable
2 years ago

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_'s avatar
    L_K_
    Icon for Helper I rankHelper I
    2 years ago

    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.