List.Max DirectQuery alternative to Group By

[PMyers]

I am attempting to list the max number in field [seg_id] based on the content of field [Identifier], as below.

You can see where [Identifier] "FG99395" has two different values in [seg_id]. I want to create another column that shows the greater of the two (or more) values associated with [Identifier]. I would normally use Group By, but I am running off DirectQuery so this isn't really an option. I've tried List.Max, but I'm clearly not understanding how to write the condition correctly. Any assistance would be greatly appreciated.

Many thanks,

Pete

[dufoq3 · Accepted solution]

Hi @PMyers, I'm not sure if this will work with DirectQuery mode (I've never used it) but try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnM3VNJRMjRQitWBcYxgHCMgxxLGMQZyTAyQeRYWFkg8U5BcLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Identifier = _t, seg_id = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"seg_id", Int64.Type}}),
    Ad_MaxSegId = Table.AddColumn(ChangedType, "max seg_id", each List.Max(Table.SelectRows(ChangedType, (x)=> x[Identifier] = [Identifier])[seg_id]), type number)
in
    Ad_MaxSegId

[PMyers]

This code works almost perfectly. I say almost, as sadly it doesn't work in DirectQuery. It appears I will have to find an alternative way of doing what I hoped to do. My thanks to you for providing the backend code. I will mark this as a solution, as the code is fine, even though nothing seems to do what I am after in DirectQuery mode.

Thanks again for your help.

Pete

[dufoq3]

You're welcome.

[Anonymous]

Hi @PMyers ,

I suggest you try DAX as follows.

Add custom column and try this DAX.

seg_ST = CALCULATE(MAX('Table'[seg_id]),ALLEXCEPT('Table','Table'[identifier]))

Here is my test for your reference. I am getting max Value1 according to each Depot.

Best regards,

Rimmon

[PMyers]

Many thanks for that. 

I can't use CALCULATE or other iteration functions like MAXX, as the table is DirectQuery. As far as I am aware, if I create a new table that relies on a DirectQuery table (eg, DISTINCT(table[Identifier]), then it will not refresh when someone else uses the report in the service. 

Thanks again for going through the effort to assist, but I am kind of hoping for a backend solution to this, if at all possible.

Cheers,
Pete