Alternative to MAX

[taumirza]

is there any function which gives us max of values in terms of strings?

not integer 0's and true or false

it should return the column value.

[OwenAuger · Accepted solution]

@taumirza

You can use LASTNONBLANK to return the 'maximum' of a text column.

See this article on SQLBI:

https://www.sqlbi.com/articles/alternative-use-of-firstnonblank-and-lastnonblank/

[OwenAuger · Accepted solution]

@taumirza

You can use LASTNONBLANK to return the 'maximum' of a text column.

See this article on SQLBI:

https://www.sqlbi.com/articles/alternative-use-of-firstnonblank-and-lastnonblank/

[taumirza]

Hi @OwenAuger,

Actually i found this blog after a little search.

Anyway thank you so much for your reply.

[taumirza]

Hi @OwenAuger,

The LastNonBlank and FirstNonBlank fuctions giving me the first and last nonblank strings but i need values with maximum character length in the column.!!

[OwenAuger · Accepted solution]

Hi @taumirza,

That's right, LASTNONBLANK/FIRSTNONBLANK order text lexicographically.

If you want the text value with greatest length, you can do this or something similar:

Longest Text Value =
CALCULATE (
    LASTNONBLANK ( MyTable[TextColumn], 0 ),
    TOPN ( 1, VALUES ( MyTable[TextColumn] ), LEN ( MyTable[TextColumn] ) )
)

If there are ties, they are broken by LASTNONBLANK.