Forum Discussion

PMyers's avatar
PMyers
Frequent Visitor
2 years ago
Solved

List.Max DirectQuery alternative to Group By

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 ...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    PMyers
    Frequent Visitor
    2 years ago

    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