Sorting on a specific column [issue]

[Anonymous]

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

[dedelman_clng · Accepted solution]

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

[Anonymous]

I haven't used this before but it seems the implementation of ADDCOLUMNS has too few arguments

[dedelman_clng]

Corrected it above....had = where there should've been a comma

[Anonymous]

thank you for your correction i seem to still be doing something wrong in this regard 

CategoryTab = 
  ADDCOLUMNS(
     SUMMARIZE(Sheet1, Sheet1[Category]),
    "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
)
)

Do i need to make a new table for this and then run this it tells me
"The expression refers to mutiple columns. Multiple columns cannot be converted to a scalar value"

Thank you for your help yet again

[dedelman_clng]

Yes, you should use "New Table" (instead of "New Measure" or "New Column") on the Modelling tab, and then add the DAX as the table definition.

[Anonymous]

Thank you this worked

[Anonymous]

Would there happen to be away to remove the null? I know that i can just uncheck blanks but when i then make a relationship between my dataset and the new table it won't because of the null value

My code

CategoryTab = 
  ADDCOLUMNS(
     SUMMARIZE(Sheet1, Sheet1[Category]),
    "Category #", SWITCH(Sheet1[Category], 
    "Technology", 1,
    "Process Management", 2,
    "Health & Safety", 3,
    "Environmental Sustainability", 4,
    "Emergeny Preparedness & Response", 5,
    "Communication & Engagement", 6,
    "Contract Management", 7,
    "Infection Prevention & Control", 8
)
)

What is displayed

And what is displayed when trying to make a relationship (this does work with preview feature composite models enabled, but it then won't allow me to publish which is essentially useless in my case)

[dedelman_clng]

CategoryTab = 
  ADDCOLUMNS(
     FILTER( SUMMARIZE(Sheet1, Sheet1[Category]), Sheet1[Category] <> "")
    "Category #", SWITCH(Sheet1[Category], 
    "Technology", 1,
    "Process Management", 2,
    "Health & Safety", 3,
    "Environmental Sustainability", 4,
    "Emergeny Preparedness & Response", 5,
    "Communication & Engagement", 6,
    "Contract Management", 7,
    "Infection Prevention & Control", 8
)

You may also want to confirm that it is ok to have a record on the fact table that doesn't reference a category.

[Anonymous]

Thank you so much this seems to be working now