DAX: substitution of all Cyrillic letters with English letters (transliteration)
Hi all DAX gurus. My question is easy to solve in Excel with VBA but in Power BI (for me) I was bit stucked but find my way and question is about optimisation! So, column has _calculated_ values as string e.g. "СТОЛ","СТУЛ","ДВЕРЬ" in Russinan language. I would like to make those values readable for foreners and use transliteration and get them in new column written with latin letters as "STOL","STUL","DVER". DAX SUBSTITUTE is looks like solution but only one by one charcter. Why in DAX and not in M? Because column values are calculated (extracted from another column using specific logic). So I add very simple but not optimal formula to get transliterated values: Result translit = VAR A1 = SUBSTITUTE([Column]," ","") VAR A2 = SUBSTITUTE(A1,"А","A") VAR A3 = SUBSTITUTE(A2,"Б","B") VAR A4 = SUBSTITUTE(A3,"В","V") VAR A5 = SUBSTITUTE(A4,"Г","G") VAR A6 = SUBSTITUTE(A5,"Д","D") VAR A7 = SUBSTITUTE(A6,"Е","E") VAR A8 = SUBSTITUTE(A7,"ё","E") VAR A9 = SUBSTITUTE(A8,"Ж","ZH") VAR A10 = SUBSTITUTE(A9,"З","Z") VAR A11 = SUBSTITUTE(A10,"И","I") VAR A12 = SUBSTITUTE(A11,"К","K") VAR A13 = SUBSTITUTE(A12,"Л","L") VAR A14 = SUBSTITUTE(A13,"М","M") VAR A15 = SUBSTITUTE(A14,"Н","N") VAR A16 = SUBSTITUTE(A15,"О","O") VAR A17 = SUBSTITUTE(A16,"П","P") VAR A18 = SUBSTITUTE(A17,"Р","R") VAR A19 = SUBSTITUTE(A18,"С","S") VAR A20 = SUBSTITUTE(A19,"Т","T") VAR A21 = SUBSTITUTE(A20,"У","U") VAR A22 = SUBSTITUTE(A21,"Ф","F") VAR A23 = SUBSTITUTE(A22,"Х","KH") VAR A24 = SUBSTITUTE(A23,"Ц","TS") VAR A25 = SUBSTITUTE(A24,"Ч","CH") VAR A26 = SUBSTITUTE(A25,"Ш","SH") VAR A27 = SUBSTITUTE(A26,"Щ","SHCH") VAR A28 = SUBSTITUTE(A27,"Ь","") VAR A29 = SUBSTITUTE(A28,"Ы","Y") VAR A30 = SUBSTITUTE(A29,"Ъ","") VAR A31 = SUBSTITUTE(A30,"Э","E") VAR A32 = SUBSTITUTE(A31,"Ю","YU") VAR A33 = SUBSTITUTE(A32,"Я","YA") VAR A34 = SUBSTITUTE(A33,"Й","I") RETURN A34 Question: is there suggestions to make it in more optimal/effective way?