How do I create a meaure in order to sort categories in an order I want to dictate?

[Rose1976]

Hi, I need to sort my categories into a specic order, I have created a new table giving each category a unique number and connected this table to the categories in relationships.  However, I believe I need to create a measure in order to make the sort work.  Not sure what the code should be, can anyone help me please?

[Reid_Havens · Accepted solution]

Hi there.

Happy to help. Some clarifying questions. Are you trying to sort the category order in a slicer/filter, or in a table? If you're trying to create a "sort column" then you don't need a seperate table, you can create a sort column on whatever table the categories are in using the SWITCH function.

Example:

=
SWITCH (
    TRUE (),
    Table[Category] = "Value A", 1,
    Table[Category] = "Value B", 2,
    BLANK ()
)

If you include a TRUE() in the SWITCH expression, it checks each statement, and if true returns the value you give it. I'm giving it a numerical value since DAX understands how to sort those. Then you use the "Sort by Column" function in the table view and sort your category column by this new (1,2,3) sort colum. Let me know if this helps!

Reid Havens - Owner

Havens Consulting Inc.

[v-caliao-msft · Accepted solution]

@Rose1976,

I have created a new table giving each category a unique number and connected this table to the categories in relationships.

When sorting a column, you can only use the columns inside the same table. So you need to create a calculated column in your orginal table. You can use Reid_Havens's expression to create the order column.

Regards,

Charlie Liao

[Reid_Havens · Accepted solution]

Hi there.

Happy to help. Some clarifying questions. Are you trying to sort the category order in a slicer/filter, or in a table? If you're trying to create a "sort column" then you don't need a seperate table, you can create a sort column on whatever table the categories are in using the SWITCH function.

Example:

=
SWITCH (
    TRUE (),
    Table[Category] = "Value A", 1,
    Table[Category] = "Value B", 2,
    BLANK ()
)

If you include a TRUE() in the SWITCH expression, it checks each statement, and if true returns the value you give it. I'm giving it a numerical value since DAX understands how to sort those. Then you use the "Sort by Column" function in the table view and sort your category column by this new (1,2,3) sort colum. Let me know if this helps!

Reid Havens - Owner

Havens Consulting Inc.