Forum Discussion
Sort by column within sub groups
Hi andhiii102030 ,
What problem do you encounter here? I think concatenating strings is a valid solution.
Some tricks like to add space after "is" is not a solution to create unique values in the table: "is " or "is "Here are my steps:
1.Creating an index column after grouping in Power Query(The index column is not used for the final sort)
2.Use the following DAX expression to create columns
Column = [Count.Sort column] & REPT(" ",[Index])Column 2 = SWITCH( TRUE(), [Count.KPI] = "costs" && [Count.Sort column] = "is",1, [Count.KPI] = "costs" && [Count.Sort column] = "calculated",2, [Count.KPI] = "costs" && [Count.Sort column] = "should",3, [Count.KPI] = "profit" && [Count.Sort column] = "is",2, [Count.KPI] = "profit" && [Count.Sort column] = "calculated",1 )3.Final output
Best Regards,
Wenbin Zhou
But, this do not work: Because "is" has value 1 and 2. And this is right, because of the different sorting in the categories.
Super UserIt looks like you’re trying to create a custom sorting order for your KPIs using the SWITCH function in DAX, but you’re encountering an issue because the value “is” appears in both the “Costs” and “Profit” groups with different sort orders.
To resolve this, you can use a nested SWITCH function or a combination of IF statements to ensure that each condition is unique. Here’s an updated version of your measure:
SortOrder =
SWITCH(
TRUE(),
[KPI-group] = "Costs" && [KPI] = "is", 1,
[KPI-group] = "Costs" && [KPI] = "calculated", 2,
[KPI-group] = "Costs" && [KPI] = "should", 3,
[KPI-group] = "Profit" && [KPI] = "is", 4, -- Changed to 4 to avoid conflict
[KPI-group] = "Profit" && [KPI] = "calculated", 5, -- Changed to 5 to avoid conflict
-- Add more conditions as needed for other groups
BLANK()
)
By assigning unique values to each condition, you can avoid conflicts and ensure that your sorting works correctly. If you have more conditions to add, just make sure each combination of [KPI-group] and [KPI] has a unique sort order value.
You can not sort the KPI column if in the sort column are different values for "is".
For my seems that i talk to a KI bot....