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 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.
Solved! Go to Solution.
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
Proud to be a Super User!
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".
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
Thanks but doesn't work because sorting depends from the total length of the string, no matter where 'unichar' is
Can you share a file and example that would be similar to your senario?
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
Proud to be a Super User!
Hello ,thanks but I don't understand: does dynamic format string allows to treat measure as number and not as text?
Hi @letizia ,
with dynamic format string you are basically applying a format to a number but not changing its data type so it will still be treated as a number that just appears in either in percentage, currency, etc.
Proud to be a Super User!
Hello, how can I manage blank rows using dynamic format strings? Here is like blank rows are considered as zero in sorting, and I don't want that. I want blank rows at the end (or at the beginning)
Hello, thank you, it works.
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |