# What is the DAX equivalent of this excel formula to find the max (highest alphabetically) value in a text column • ### Question

• I am trying to do the following but in PowerPivot, not a standard Excel formula. Can anyone help?

{=INDEX(A2:A8,MATCH(MAX(COUNTIF(A2:A8,”<”&A2:A8)),COUNTIF(A2:A8,”<”&A2:A8),0))}{=INDEX(A2:A8,MATCH(MAX(COUNTIF(A2:A8,”<”&A2:A8)),COUNTIF(A2:A8,”<”&A2:A8),0))}

Original article here: Max Text

Thanks,

James

Friday, January 9, 2015 4:56 PM

• ```LastAlphaValue=
TOPN( 1
, VALUES( <tableX>[<fieldX>] )
, <tableX>[<fieldX>]
, 0
)```

This should work as a measure or a calculated column.

Here's what's happening:

TOPN() returns the top N rows from a table based on a sort order you define. It takes at least 3 arguments:

1. N = number of rows to return
2. Table from which to return N rows
3. Expression to sort by
4. (Optional) Sort order: 0 = Descending (default); 1 = Ascending

You can pass multiple sort parameters in odd-numbered arguments after 4, with sort orders on even-numbered arguments after 4.

We are evaluating the top 1 row.

We are taking that row from the table created by VALUES(). VALUES() returns the unique values in a field.

We are sorting by the value of that field.

We are sorting in descending order.

• Marked as answer by Friday, January 9, 2015 6:52 PM
Friday, January 9, 2015 5:35 PM

### All replies

• ```LastAlphaValue=
TOPN( 1
, VALUES( <tableX>[<fieldX>] )
, <tableX>[<fieldX>]
, 0
)```

This should work as a measure or a calculated column.

Here's what's happening:

TOPN() returns the top N rows from a table based on a sort order you define. It takes at least 3 arguments:

1. N = number of rows to return
2. Table from which to return N rows
3. Expression to sort by
4. (Optional) Sort order: 0 = Descending (default); 1 = Ascending

You can pass multiple sort parameters in odd-numbered arguments after 4, with sort orders on even-numbered arguments after 4.

We are evaluating the top 1 row.

We are taking that row from the table created by VALUES(). VALUES() returns the unique values in a field.

We are sorting by the value of that field.

We are sorting in descending order.

• Marked as answer by Friday, January 9, 2015 6:52 PM
Friday, January 9, 2015 5:35 PM
• Hi Greg, thanks for this and thank you for the very clear explanation.
Friday, January 9, 2015 6:52 PM