Column header sorting in a matrix

[Anonymous]

I have a matrix column with headings 5 Years rolling annualized, 3 Years rolling annualized, 1 Years rolling annualized, YTD, MTD. I want to sort them this custom order, but it is getting sorted in the order 1 Years rolling annualized, 3 Years rolling annualized, 5 Years rolling annualized, YTD, MTD when i do it by creating a custom table with period column having rows 5 Years rolling annualized, 3 Years rolling annualized, 1 Years rolling annualized, YTD, MTD and sort column with rows 1, 2, 3, 4, 5 then creating relationship with the table and sorting the column with sort.
By default  order is 1 Years rolling annualized, 3 Years rolling annualized, 5 Years rolling annualized, MTD, YTD.
I also tried to fix it with Dax : 

sort =
if(CONTAINS('AFAM Portfolio Performance','AFAM Portfolio Performance'[Period Annualized],"1 Years rolling annualized"), "3",
if(CONTAINS('AFAM Portfolio Performance','AFAM Portfolio Performance'[Period Annualized],"3 Years rolling annualized"), "2",
if(CONTAINS('AFAM Portfolio Performance','AFAM Portfolio Performance'[Period Annualized],"5 Years rolling annualized"), "1",
if(CONTAINS('AFAM Portfolio Performance','AFAM Portfolio Performance'[Period Annualized],"YTD"), "4",
if(CONTAINS('AFAM Portfolio Performance','AFAM Portfolio Performance'[Period Annualized],"MTD"), "5","")))))

sort =
if(CONTAINS('AFAM Portfolio Performance','AFAM Portfolio Performance'[Period Annualized],"1 Years rolling annualized"), "C",
if(CONTAINS('AFAM Portfolio Performance','AFAM Portfolio Performance'[Period Annualized],"3 Years rolling annualized"), "B",
if(CONTAINS('AFAM Portfolio Performance','AFAM Portfolio Performance'[Period Annualized],"5 Years rolling annualized"), "A",
if(SELECTEDVALUE('AFAM Portfolio Performance'[Period Annualized])="YTD", "D",
if(SELECTEDVALUE('AFAM Portfolio Performance'[Period Annualized])="MTD", "E","")))))
But stil

[Anonymous]

I have created a table for sorting, then written a dax to relate the table and sort column with sort column of the table 

Sorting = RELATED('CALC AFAM Period'[Sort])

[Vera_33]

Hi @Anonymous 

From your screenshot, the Sorting did not bring all the index from your other table [Sort], not sure how did it happen but that's an issue why the sort is not working.

I can't replicate your issue. In my test, you go to Power BI -> Enter data, simply create your sort table, you can copy the data from Excel, or simply enter it, no need to write DAX to get Sort column. Then the rest is working.

[Anonymous]

Can someone help with the solution?

[Vera_33]

Hi @Anonymous 

You are using DAX Calculated column to do sort? Have you got Circular dependency error? How about adding a sort column in Power Query? It's better to provide some sample data along with your visual to show your problem clearly.

Table.AddColumn(yourPreviousStep, "sort", each 
if Text.Contains([Period Annualized],"1 Years rolling annualized") then 3 
else if Text.Contains([Period Annualized],"3 Years rolling annualized") then 2
else if Text.Contains([Period Annualized],"5 Years rolling annualized") then 1
else if Text.Contains([Period Annualized],"YTD") then 4
else if Text.Contains([Period Annualized],"MTD") then 5
else "")