cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors