cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper V

## Return adjacent column value based on max

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?

1 ACCEPTED SOLUTION
Super User

@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)

``````

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

@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)

``````

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.