Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
kent-culpepper
Frequent Visitor

List.Max function @ Power Query M (Inline Subquery)

I am trying to Add a Column ("Max_Quote_Date") that returns the Max Date of another Column ("Quote_Date") with 2 conditions ("Investment_Key","Month_Date"), using the following expression;

 

 

#"Added Max Quote Date" = Table.AddColumn(#"Filtered Rows1", "Max_Quote_Date", each 
    List.Max(#"Filtered Rows1"[Quote_Date], 
            [Investment_Key] = #"Filtered Rows1"[Investment_Key] and [Month_Date] < #"Filtered Rows1"[Month_Date]
        )
    )

 

 

It doesnt seem to be applying both conditions. And the performance is very slow.. I am sure there is a more efficient method.

 

Below is a visual illustration of what i am trying to achieve;

kentculpepper_0-1705711414501.png

 

I liken this to an "inline subquery" in terms of SQL.

 

Any ideas?

 

Thanks much for your support.

5 REPLIES 5
Ahmedx
Super User
Super User

firstly, the conditions are unclear, try to explain in words what you want and what conditions must be met
secondly, share data that will help you

Where 

[Investment_Key] = #"Filtered Rows1"[Investment_Key] and [Month_Date] < #"Filtered Rows1"[Month_Date]

[Investment_Key]  this is Value
 #"Filtered Rows1"[Investment_Key]  this is List
why do you want to compare the value with the List🙄

so I ask what do you need?

If we reference the visual, I am indicating that I want to return (in a new column) the yellow-shaded value where it is currently null.

 

Which means I want to find the Max(Quote_Date) Where the Investment_Key = the Prior Step Investment Key and the Month_Date < the Prior Step Month_Date.

 

9/30/23 = Max(Quote_Date) Where Investment_Key = Aeva Tech And Month_Date < 10/31/23.

Ashish_Mathur
Super User
Super User

Hi,

If you are amenable to the idea of solving it with a calculated column DAX formula, then share some data to work with (in a format that can be pasted in an MS Excel file).


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.