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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jski
Frequent Visitor

helper column to sort legend dynamically

Hello,

I have a dimension on legend that I need to sort dynamically. I can't add a sort order column that is hardcoded. I need the values in the sort column to populate dynamically based on another table. 

 

table 1:

case idcolor
1red
2red
3blue
4yellow
5green
6

purple

 

table 2:

colorneed a column with the case count to use to sort 'color', including a tiebreaker

so in this case, the helper column would sort red first because its case count is 2. 

 

will this approach work? and how can i create the column? 

 

Thank you!

1 ACCEPTED SOLUTION

HI @jski ,

You can consider to create a sort table with unique fields value and index fields on datasource side.
After this, you can use index column as 'sort by column as' of value field, use this value field to create relations mapping to draw table and use new table value fields as legend.(legend field sort order will changes based on index which you set up on datasource side)

If you want this table only exist on power bi side, you can extract all color value from raw table and remove duplicate value, create a calculated column with switch function to define their index.(sort order will changes if you modify formula conditions)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

HI @jski ,

Power bi not allow you to do customize on legend field sort order. You can refer to following link to create a custom sorting table to achieve your requirement:

Custom Sorting in Power BI

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

These instructions are to hardcode a sorting order and are not applicable. I need the sort to be dynamic. It seems like there should be a way to create a calculated column that can be used with changing data. Can you create a virtual table that is just a list of counts and also has logic to account for tiebreakers (due to the 'this column can't be used due to multiple values' error message)?






 

HI @jski ,

You can consider to create a sort table with unique fields value and index fields on datasource side.
After this, you can use index column as 'sort by column as' of value field, use this value field to create relations mapping to draw table and use new table value fields as legend.(legend field sort order will changes based on index which you set up on datasource side)

If you want this table only exist on power bi side, you can extract all color value from raw table and remove duplicate value, create a calculated column with switch function to define their index.(sort order will changes if you modify formula conditions)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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