Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
dedelman_clng
Community Champion
Community Champion

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

View solution in original post

9 REPLIES 9
dedelman_clng
Community Champion
Community Champion

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
Not applicable

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

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

Anonymous
Not applicable

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

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
Not applicable

Thank you this worked

Anonymous
Not applicable

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

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
Not applicable

Thank you so much this seems to be working now

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.