Return adjacent column value based on max

[CarlsBerg999]

Hi,

Im trying to create a calculcated column, that retrieves a text value from another table. The logic is the following 

VAR variable1 = blabla

VAR variable2 = blabla

VAR ReturnInvoice =  CALCULATE(MAX('Table1'[InvoiceID]),
'Table1'[Column2] = variable1,
'Table1'[Column3] = variable2,

'Table1'[Column4] = Max('Table1'[Column4]))

RETURN

ReturnInvoice

--> This however does not work. The problem is with the last filter. How do i return the InvoiceID based on the maximum value in Column4? 

[parry2k · Accepted solution]

@CarlsBerg999 try this:

VAR variable1 = blabla

VAR variable2 = blabla

VAR __max = CALCUALTE( Max('Table1'[Column4]))

VAR ReturnInvoice =  CALCULATE(MAX('Table1'[InvoiceID]),
'Table1'[Column2] = variable1,
'Table1'[Column3] = variable2,

'Table1'[Column4] = __max)