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
Here’s a solution using DAX that should help you achieve the desired sorting without creating additional tables.
Create a Calculated Column for Sorting: You can create a calculated column in your ‘fact’ table that assigns a sort order based on the KPI group and KPI name. Use the SWITCH function to handle different sorting orders for different groups.
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", 2,
[KPI-group] = "Profit" && [KPI] = "calculated", 1,
-- Add more conditions as needed for other groups
BLANK()
)
Sort by the Calculated Column: Once you have the SortOrder column, you can use it to sort your KPIs in the visual.
- Go to the ‘fact’ table in Power BI.
- Select the KPI column.
- In the ribbon, click on Sort by Column and choose the SortOrder column.
- This approach ensures that each KPI is sorted according to the specific order defined for its group.
But, this do not work: Because "is" has value 1 and 2. And this is right, because of the different sorting in the categories.
It 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....