locked
What is the DAX equivalent of this excel formula to find the max (highest alphabetically) value in a text column RRS feed

  • 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

Answers

  • 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 Maracles 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 Maracles 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