Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 # = SWITC...
Need Help
  • dedelman_clng's avatar
    dedelman_clng
    8 years ago

    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

dedelman_clng's avatar
dedelman_clng
Icon for Community Champion rankCommunity Champion
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

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

    • dedelman_clng's avatar
      dedelman_clng
      Icon for Community Champion rankCommunity Champion
      8 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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