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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
andhiii102030
Frequent Visitor

Sort by column within sub groups

Hi,

 

i have a special problem and no good solution right now for this case. I have KPI which are group by categories. 
Costs: is, calculated, should

profit: is, calculated 

andhiii102030_2-1725615911103.png

No i try to sort this KPI in their own sub group:

andhiii102030_3-1725615921992.png

 

The problem ist, that the KPI "is, calculated and should" have their order 1,2,3 

But it can happened that in a other group is and calculated have a opposite order 2,1 (see red box)

I try to solve it to create a own column for this sorting and to have the KPI-GROUP-KPI column for unique values:

andhiii102030_4-1725615977684.png

and create a relationsship via KPI-group-KPI

andhiii102030_0-1725617014690.png

 

But how it is possible to sort a column in one case after one column and in another case after a other column.
Some tricks like to add space after "is" is not a solution to create unique values in the table: "is " or "is  "
I want to avoid additional tables. 
Did someone have a smart solution? 

 

Thank you!

1 ACCEPTED SOLUTION
v-zhouwen-msft
Community Support
Community Support

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)

vzhouwenmsft_1-1726121228775.png

vzhouwenmsft_2-1726121288387.png

vzhouwenmsft_3-1726121344253.png

vzhouwenmsft_4-1726121433470.png

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
)

vzhouwenmsft_6-1726121592543.png

3.Final output

vzhouwenmsft_7-1726121617448.png

 

Best Regards,
Wenbin Zhou

 

View solution in original post

5 REPLIES 5
v-zhouwen-msft
Community Support
Community Support

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)

vzhouwenmsft_1-1726121228775.png

vzhouwenmsft_2-1726121288387.png

vzhouwenmsft_3-1726121344253.png

vzhouwenmsft_4-1726121433470.png

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
)

vzhouwenmsft_6-1726121592543.png

3.Final output

vzhouwenmsft_7-1726121617448.png

 

Best Regards,
Wenbin Zhou

 

suparnababu8
Solution Sage
Solution Sage

Hi @andhiii102030 

Here’s a solution using DAX that should help you achieve the desired sorting without creating additional tables.

  1. 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.

 

andhiii102030_0-1725618159182.png

 

Hi @andhiii102030 

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....

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.