Forum Discussion
Sorting on a specific column [issue]
Hello,
I am trying to sort a slicer in the way I would like it to be displayed.
Currently my issue lies within sorting category by a category #
my category # code currently:
Category # =
SWITCH(Sheet1[Category],
"Technology", 1,
"Process Management", 2,
"Health & Safety", 3,
"Environmental Sustainability", 4,
"Emergency Preparedness & Response", 5,
"Communication & Engagement", 6,
"Contract Management", 7,
"Infection Prevention & Control", 8
)now when i go to sort category by category # i get this issue...
Any help would be greatfully appreciate. I have used sorting for months in the past by sorting off month # and make use of it with fiscal year sorting, not sure where my head is at on this one, but I can't seem to solve my problem
Thanks
You might want to try to make this a separate calcaulted dimension table, then have a relationship back to the fact table. Something along the lines of:
CategoryTab = ADDCOLUMNS( SUMMARIZE(FactTable, FactTable[Category]), "Category #", [Insert your switch formula here] )You should then be able to sort Category by Category # in this table, and use this new table in the slicer.
Hope this helps
David
9 Replies
You might want to try to make this a separate calcaulted dimension table, then have a relationship back to the fact table. Something along the lines of:
CategoryTab = ADDCOLUMNS( SUMMARIZE(FactTable, FactTable[Category]), "Category #", [Insert your switch formula here] )You should then be able to sort Category by Category # in this table, and use this new table in the slicer.
Hope this helps
David
Corrected it above....had = where there should've been a comma