Creating an Index column vs Measure to sort by

[Clint]

 Hello,

I have a Matrix table of Projects that is has the following hierarchy:
Suite Release (level 1)

Project Name (level 2)

I need a semi custom sort order by Suite Release (which is a alphanumeric column) so I used the following Dax to create this index:

VAR SRIndex=SELECTEDVALUE('Projects'[SuiteRelease])

Return

SWITCH(TRUE(),

SRIndex = "In Flight",1,

SRIndex = "OoC",2,

SRIndex = "G2E 2019",3,

SRIndex = "HALo 3800 MR1",5,

SRIndex = "HALo 3800 MR2",5,

SRIndex = "Suite Release 1",4,

SRIndex = "Suite Release 1A",4,

SRIndex = "Suite Release 1B",4,

SRIndex = "Suite Release 1C",4,

SRIndex = "Suite Release 1D",4,

SRIndex = "Suite Release 1E",4,

SRIndex = "Suite Release 1F",4,

SRIndex = "Suite Release 2",4,

SRIndex = "Suite Release 3",4,

SRIndex = "Suite Release 4",4,

SRIndex = "Suite Release 6",4,

SRIndex = "Suite Release 5",4,

SRIndex = "Suite Release 7",4,

SRIndex = "MR",6

)

This works AS A MEASURE - and sorts things correctly.  However, I cannot sort the Suite Release column by a Measure.  When I use this same Dax and create a column, it doesn't return anything.  No errors, it just doesn't return any values when I put it in a table w/the Suite Release column and/or project name column.  
Is it possible to create a column that does this index or, if not, how do I hide the measure from my table but still sort by it?

Thanks in advance for any assistance.

[d_gosbell]

I'm pretty sure that SELECTEDVALUE requires a filter context and in a calc column you only have a row context. But you should be able to simplify this down to the following pattern.

SWITCH('Projects'[SuiteRelease],

"In Flight",1,

"OoC",2,

"G2E 2019",3,

"HALo 3800 MR1",5,

...  etc

[Clint]

Interestingly, even if I add this column to the "values" in the matrix table and set the table to sort by this field, it doesn't sort right.  If I convert the table to a regular flat table, it does sort correctly.  Not sure why this would be the case?

[Clint]

Changing the column type to "Text" from "Number" now sorts the matrix table correctly.  So, if there's a way to hide the column from the visual, this would give me what I need.

[Clint · Accepted solution]

Found this thread (https://community.powerbi.com/t5/Desktop/quot-Sort-by-column-quot-from-a-related-table/td-p/88907) and used it as guide and was able to get the sort to work correctly.

[d_gosbell]

---

@Clint wrote:

Found this thread (https://community.powerbi.com/t5/Desktop/quot-Sort-by-column-quot-from-a-related-table/td-p/88907) and used it as guide and was able to get the sort to work correctly.

---

Yes, this is the way I would have done it. Using the new index column as the sort by column for the original column.

[Clint]

Thank you D_Gosbell.  I was able to create the column and it returned the correct values.  However, I still can't sort the Suite Release column table (in a matrix table which has a hierarchy of Suite Release >Project Name) by this index column.  I get the error "This column can't be sorted by a column that is already sorted, directly or indirectly, by this column.  Is there a way to add this column to the table but hide it?  I thought this was an option but I'm not sure how to do this.