sorting

[letizia]

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.  

[achandelkar6]

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".

[Ahmedx]

show me what you write, what doesn’t work for you, at least share screenshots

[letizia]

Here, from left to right: what I want to sort, measure I use, measure I'm trying to apply

[Ahmedx]

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

[letizia]

Thanks but doesn't work because sorting depends from the total length of the string, no matter where 'unichar' is

[Ahmedx]

Can you share a file and example that would be similar to your senario?

[danextian · Accepted 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 

[letizia]

Hello ,thanks but I don't understand: does dynamic format string allows to treat measure as number and not as text?

[danextian]

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.

[letizia]

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) 

[Ahmedx]

refer to the following

https://www.youtube.com/watch?v=kfzIsSkFPgU

[letizia]

Hello, thank you, it works.