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

 

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

5 Replies

  • 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

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

      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