Get min text value for each ID based off another column

[VOx15]

Hello, 

I have been stuck on this for serveral days and have gotten closed but this dax fomula returns some blanks and it should not. 

I have a data table that has an ID column, Start of month column, Officer name. The ID field repeats because there is an ID for every month. The officer name can change as the months go on. 

What i need is a new column that gives me the original officer for each loan. So, far i have this dax:

Original Officer =

VAR MinTime = MIN(StartofMonth)

RETURN

CALCULATE(FIRSTNONBLANK(Officer,1),

FILTER(ALLEXCEPT( Table, ID ), StartofMonth=MinTime),

ID=EARLIER(ID))

Here is what the result should look like:

[v-eqin-msft]

Hi @VOx15 ,

According to my understand, you want to specify the Officers based on each ID and the earliest date ,right?

You could use the following formula:

rank =
RANKX (
    FILTER ( ALL ( Table5 ), 'Table5'[ID] = MAX ( 'Table5'[ID] ) ),
    CALCULATE ( MAX ( ( 'Table5'[StartofMonth] ) ) ),
    ,
    ASC
)

Original =
CALCULATE (
    MAX ( Table5[Officer] ),
    FILTER ( ALL ( Table5 ), 'Table5'[ID] = MAX ( Table5[ID] ) && [rank] = 1 )
)

My visualization looks like this:

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[Greg_Deckler · Accepted solution]

@VOx15 This is a bit different if you are going for a measure or a calculated column. Looks like a measure in your case, should be:

Measure =
  VAR __ID = MAX([ID])
  VAR __MinDate = MINX(FILTER(ALL('Table'),[ID]=__ID),[StartofMonth])
RETURN
  MAXX(FILTER(ALL('Table'),[ID]=__ID) && [StartofMonth]=__MinDate),[Officer])


Column = 
  VAR __ID = [ID]
  VAR __MinDate = MINX(FILTER(ALL('Table'),[ID]=__ID),[StartofMonth])
RETURN
  MAXX(FILTER(ALL('Table'),[ID]=__ID) && [StartofMonth]=__MinDate),[Officer])

Basically, this is a Lookup Min/Max pattern. https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

[VOx15]

@Greg_Deckler thank you! Worked great!

[amitchandak]

@VOx15 ,

column =
var _max = max(filter(Table,[ID] =earlier([ID]) && [DAte] <earlier([Date])),[Date])
return
coalesce(max(filter(Table,[ID] =earlier([ID]) && [DAte] =_max),[officer]),[officer])