cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ChrisPollock Regular Visitor
Regular Visitor

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...
Capture.PNG
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

1 ACCEPTED SOLUTION

Accepted Solutions
dedelman_clng New Contributor
New Contributor

Re: Sorting on a specific column [issue]

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 9
dedelman_clng New Contributor
New Contributor

Re: Sorting on a specific column [issue]

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

ChrisPollock Regular Visitor
Regular Visitor

Re: Sorting on a specific column [issue]

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

dedelman_clng New Contributor
New Contributor

Re: Sorting on a specific column [issue]

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

ChrisPollock Regular Visitor
Regular Visitor

Re: Sorting on a specific column [issue]

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 New Contributor
New Contributor

Re: Sorting on a specific column [issue]

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.

ChrisPollock Regular Visitor
Regular Visitor

Re: Sorting on a specific column [issue]

Thank you this worked

ChrisPollock Regular Visitor
Regular Visitor

Re: Sorting on a specific column [issue]

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 displayedCapture.PNG

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)Capture.PNG

dedelman_clng New Contributor
New Contributor

Re: Sorting on a specific column [issue]

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.

ChrisPollock Regular Visitor
Regular Visitor

Re: Sorting on a specific column [issue]

Thank you so much this seems to be working now