Hi -
I have a query that results in a table that lists a repeating group of like records with varying revision numbers (reflected in a separate field. I’d like to query this table for a list of the Items with there highest revision number. This is what I’m after:
I’ve tried some things with Group By but can’t seem to get what I’m after. I’d sure appreciate anyone’s help!
Cheers,
Alex
You need a calculated field that uses
Max(yourfield)
Thanks for your reply!
Hmmm… I’ve tried adding a calculated field of max(RevNum) but get an error: “… not contained in an aggregate function of group by clause…”.
Being fairly new to this, I’m sure I’m missing something. Would you be able to elaborate on how to use max() in a calculated field?
Thanks Much!
A.
You have to check the box of what you want to group by.
Can you show some screen shots of what you have so far? And, did you go to the Tools User guide and read through that? It should walk you through some of this stuff. Read through the BAQ section first, then come back and let us know where you’re still confused.
I’m a window function enthusiast myself. Group By requires: You have to capture irrelevant fields in the Group By statement, which means your query breaks when you add or remove any field, even when it’s completely irrelevant to the aggregation you’re doing. Performing multiple aggregations only using Group By often turns into a mess of inline table expressions so they don’t trip over each other. And Group By scatters what would be an easy to read single expression across multiple places in your query.
So, you could do like this:
max(yourfield) over(partition by yourGroupingField) as yourAlias
For example, if the sales manager asks for the highest UnitPrice for each gross weight UOM (why? probably better off not thinking too hard about it…), I’d write:
max(Part.UnitPrice) over(partition by Part.GrossWeightUOM) as MaxWeightMetricPrice
And that’s it. The sales manager’s so happy with that, now they want to see Min, Avg, Sum, and StdDv! We just add the calculated fields and it’s done. Add or remove lines, the query just runs. Aggregation is entirely constrained to its Select line.
Normally I’d suggest passing off data transforms like this to power query, but power query doesn’t really have an analogue for window functions that isn’t both clunky and orders of magnitude less performant than just running it in SQL.