How to get the first or last value in a column?

[shaoboji]

Hi everyone, need a little help here:

In a table like this:

date        rank

1/1/2000    2

1/2/2000    1

1/3/2000    3

1/4/2000    2

I want to get the first or last value of rank, so both are 2 in this case. But with FirstNonBlank or LastNonBlank, the result is always in asc or desc order, getting 1 and 3. Any ideas? Thanks.

[Anonymous · Accepted solution]

@shaoboji  - You can creat a Measure like this:

Find First = 
var __EarliestDate = MIN(YourTable[Date])
return CALCULATE(MIN(YourTable[Rank]), YourTable[Date] = __EarliestDate )

Hope this helps,

Nathan

[DouweMeer]

@Anonymous 

I was curious. Why are you using "__" in front of the VAR name?

[Anonymous]

@DouweMeer  - I don't always use it, but it's a way to easily identify variables when reading the code. Notably, visuals use this format for variables in their behind-the-scenes DAX. It also offers some protection from re-using a keyword or existing column/measure/table name.

[DouweMeer]

@Anonymous  Fair enough. Thought it might had something to do with standard practice in other languages.