Forum Discussion
Helper VCreating an Index column vs Measure to sort by
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.
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.
6 Replies
- d_gosbell
Super User
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
ClintThank 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.
- 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.
