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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Martin_Bruwer
Frequent Visitor

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

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

6 REPLIES 6
Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Martin_Bruwer,

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

Thanks a lot,

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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