powerbidax

FIRSTNOTBLANK (DAX) Power BI - Without Auto-ordering

it seems like PowerBI is auto-ordering my table and I don't know why. So, I have this table (that I exported from a Excel file)

Colonne 1
7
25
1
8
3

Whenever I do a New Mesure, with this formula:

Mesure = FIRSTNONBLANK(Feuil1[Colonne 1];0)

It gives me the number 1. If I do LASTNONBLANK, it returns me the number 25. So it clearly auto-order my dataset, but whenever I look in the «Data» tab of the software, I see that my data are ordered the way I want them to be.

Any idea on how to keep the default ordering?

Solution

  • Unfortunately, that's the exact behavior of FIRSTNONBLANK() and LASTNONBLANK(). They iterate the values of a column based on their native sort order (i.e. data type). Therefore, FIRSTNONBLANK would return the smallest number 1 and and LASTNONBLANK the largest number 25. And in general, you cannot make assumptions on sort order of values in a table or in a column when using DAX.

    Therefore, my suggestion is that you can explicitly program the logic to find the target value. Say the table is sorted based on date, you can find the key of the earliest/latest date (based on your use case), then lookup the value using the key.

    Target = 
VAR TargetKey = 
CALCULATE(
    VALUES(Table1[Key]),
    FILTER(
        Table1,
        Table1[Date] = MIN(Table1[Date]) // or MAX(Table1[Date])
    )
)
RETURN
LOOKUPVALUE(Table1[Colonne 1], Table1[Key], TargetKey)

    dax

    result