Search code examples
exceldaxpowerpivot

DAX MIN Function for Strings in Excel 2016

DAX MIN function can be used with numbers, dates and Strings as far as I know (See dax.guide/min/). However, it doesn't seem to work for Strings in Excel-2016.

Here's a small example : Excel-2019:

enter image description here

No error in PowerPivot :

enter image description here

But an error occurs in Excel-2016 :

enter image description here

(Sorry the message is in French. My translation: Semantic error: MIN function uses an argument which can be numbers or dates, but cannot work with values of type String.)

I was advised to use it for getting a unique value excel-pivot-table-data-model-measure-for-displaying-a-value-without-calculation. But the proposed formula generates an error in Excel-2016.

The formula is :

WorkingRate :=
VAR ThisPerson =
    MIN( table_hr_activity[person] )  ← error here in Excel-2016, person being a String
RETURN
    IF(
        ISFILTERED( table_hr_activity[activity] ),
        BLANK(),
        LOOKUPVALUE(
            table_human_resources[working rate],
            table_human_resources[person], ThisPerson
        )
    )

What could be the workaround? I haven't been able to find any solution.

Solution

  • You can use FIRSTNONBLANK(table_hr_activity[person],1) as an alternative to MIN for String columns.

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