[Power Query] return max value indicator filtered by another column

[Martin_Bruwer]

Good Afternoon,

I am trying to figure out how I can recreate my DAX column in Power Query.

Max Inv Value = if(CALCULATE(max(Revenue_MIP[Value]),FILTER(Revenue_MIP,Revenue_MIP[Patient ID]=earlier(Revenue_MIP[Patient ID])))=Revenue_MIP[Value],"Max","")

This is giving me the "Max" indicator I need but I need to accomplish this step as part of my ETL.

Thank you

Martin

[Greg_Deckler]

So, there is a Power Query technique for referencing a different row in your query. You might be able to adapt this to what you are trying to accomplish:

=if [Index] = 0 then fnSierpinskiInit("0,1") else fnSierpinskiInit(#"Renamed Columns"{[Index]-1}[Sierpinski])

[Martin_Bruwer]

Hi Greg,

Thanks for the reply, I'm afraid my Q skills are probably solidly average, so I am not sure how your suggestion would help me or even what it is actually doing.

Martin

[Greg_Deckler]

Well, I noticed that you are using EARLIER in your DAX. It looks like you are trying to grab the MAX value of a column that meets certain criteria defined by your current row. 

So, the M code I provided, you can do something similar in Power Query by using the syntax {[Index]-1} if you have an Index column. So, you are referring to the previous row. My thought is that you might be able to extend that concept since I believe that is returning a list. But, I haven't hashed all of that out. And, if the max was after the current row, you'd probably have to use a Table.Buffer probably. Anyway, @ImkeF will likely be of much greater assistance on this than I.

[Martin_Bruwer]

Thanks Greg,

I am using the EARLIER function simply as a this line function, I put variables in later.

Hopefully @ImkeF will be able to help as you have suggested. Having to go outside of Power BI to do this now which is less than ideal.

[Greg_Deckler]

Right, you are essentially getting a list of all of the other rows in the table that have the same value as the current row and then taking the MAX of those rows. Thus, for each row, you need to do a calculation that involves other rows in the table. In general, that's not the typical use case for building queries. I suspect that the solution will involve Table.Buffer and a custom function where you feed it your Source line and your filtering value and the function returns the max of the rows.

[ImkeF]

Hi @Martin_Bruwer,

could you please share some sample data: Table before and table (with new column) after?

Thanks a lot,