Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 id | color |
1 | red |
2 | red |
3 | blue |
4 | yellow |
5 | green |
6 | purple |
table 2:
color | need 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!
Solved! Go to 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
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:
Regards,
Xiaoxin Sheng
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |