Forum Discussion
How to sort measure by another column
Hi Ritaf1983,
It did work for me.
Correct me one thing, is the dynamic measure formatting is not suported in direct query mode?
In import mode it worked.
Hi Ritaf1983,
It did work for me.
Correct me one thing, is the dynamic measure formatting is not suported in direct query mode?
In import mode it worked.
Super UserHi Anonymous
I must point out that DirectQuery is my weak spot, as I know it only from documentation and not from personal experience. However, I am familiar with this note regarding the topic:
**DirectQuery over AS**: When you click "Make changes to this model" on a live connected report, this shifts the connection to DirectQuery over AS. Generally, this allows you to make changes to the format strings of the remote model measures. For the public preview of dynamic format strings for measures:
Remote model measures with dynamic format strings defined will be blocked from making changes to the format string, whether to a static format string or a different dynamic format string DAX expression. Remote model measures cannot be changed from a static format string to a dynamic format string DAX expression defined in the local model. Local model measures will also be blocked from using dynamic format strings for measures. These restrictions are being explored and may change in future releases of Power BI Desktop.
I would still try, and if it doesn’t work, I don’t think there’s a solution for the sorting issue.
Additionally, when I looked at your question again, I noticed another potential issue regarding table readability. When you apply different formats based on the size of numbers, the reader's brain might get confused. A number that looks shorter could actually be larger in value. For example:
- 1M
- 100K
Even though **100K** has more characters, **1M** is actually larger. So it might be worth splitting the data into several groups based on their size—like large, medium, and small—using three separate smaller tables. This way, you avoid the confusion and can place the units in the header instead of repeating them in every cell and solve the technical issue because you will have only numbers.
Like sales K$
100
50
Sales in Bn
8
2
etc...
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi Ritaf1983 ,
Thanks for answers. It seems dynamic formatting wont work with direct query mode.- Ritaf1983
Hi Anonymous
So unfortunately, I doubt there’s an elegant way to handle this 😞 It’s possible to add a ranking column to the table, but it’s still an additional column... And every time a user tries to sort by the actual amounts, the sorting will break.
i attached a pbix with this option just to be on the safe side 🙂
And it’s really, really problematic in that same visualization to mix formats. I did an experiment like this in one of the meetups I led on data visualization, and people either got confused or took time to understand who’s bigger than whom in such cases. So maybe in this specific case, it’s more of a feature than a bug 🙂
P.s you marked your reaction to me as a solution is this what you meant or it is a mistake?If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi Ritaf1983 ,
Thanks a lot for your effort.
yes creating a rank column will not work since the users always want to sort the sales measure column only.
Seems like no way we can do it.