Forum Discussion
sorting
Hello, I have a formatted measure (so a text) which I want to sort by numerical value. My issue is that I have both negative and positive value, so even if I try to use 'UNICHAR' method adding "0" and then sort them, this doesn't work because of negative values. Any ideas? Thanks in advance.
Hello,
Instead of using the FORMAT function which returns text, have you tried dynamic format strings?
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings
12 Replies
Hello,
Instead of using the FORMAT function which returns text, have you tried dynamic format strings?
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings
Hello ,thanks but I don't understand: does dynamic format string allows to treat measure as number and not as text?
show me what you write, what doesn’t work for you, at least share screenshots
Here, from left to right: what I want to sort, measure I use, measure I'm trying to apply
Insert this measure before positive numbers
REP(UNICHAR(8203),x).
and where are the negatives after them.
for example:
REP(UNICHAR(8203),x)&[positive]
[nigativ]&REP(UNICHAR(8203),x)
X is how many times to repeat
NumericalSort =
VAR NumericValue =
IF(
LEFT([FormattedMeasure], 1) = "-",
-1 * VALUE(SUBSTITUTE([FormattedMeasure], "-", "")),
VALUE([FormattedMeasure])
)
RETURN
UNICHAR(8203) & TEXT(NumericValue, "0")
UNICHAR (8203) & TEXT(NumericValue, "0") - This section adds a zero-width space using UNICHAR(8203) to ensure proper sorting. The numerical value is then converted back to text using the Text function with a specified format of "0".
