Forum Discussion

enieuwer's avatar
enieuwer
Frequent Visitor
5 years ago

Find the next nearest value with filtering

Hi All,

 

I have a table that consists of objects. These objects consist of several parts that are numbered (ascending). 
What I would like to do for each 'short' part is to find the nearest (ascending) 'long' part. 

I tried to visualize it by the image below, where 'Next long Object_Part' is the outcome I am trying to retrieve.

 

Because my datafile consist of hundreds of Objects and thousands of Parts it has to be robust. I already tried to do so by declaring a variable, but I keep getting 'time out' messages.

 

Thanks in advance!

1 Reply

  • daxer-almighty's avatar
    daxer-almighty
    Icon for Solution Sage rankSolution Sage
    5 years ago

    You're getting a timeout because such things should be calculated in Power Query, not in DAX.

     

     

    // Objects
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIE4uKM/KISIO0Yb6gUqwMRN0IRN4KLGwNxTn5eOljYGC5sgqLcBC5uiqzcFCzshGarE9RWJzRbnaC2OkFtRYgbw8VNEMY7gWyNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Object = _t, Part = _t, Short_Long = _t, Object_Part = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Object", type text}, {"Part", Int64.Type}, {"Short_Long", type text}, {"Object_Part", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "NextLongPart", 
        each
            if _[Short_Long] = "long" then null else
            let
                CurrentObject = _[Object],
                NextLongPart = List.Min(
                    Table.SelectRows(
                        #"Changed Type",
                        (r) => 
                            r[Object] = _[Object]  // rows with same object
                            and 
                            r[Short_Long] = "long" // which are long
                            and
                            r[Part] > _[Part] // and whose Part is after the current row's Part
                    )[Part],
                    null
                ),
                ObjectPart = if NextLongPart <> null then
                    Table.SelectRows(
                        #"Changed Type",
                        each [Object] = CurrentObject and [Part] = NextLongPart
                    )[Object_Part]{0}
                else null
            in
                ObjectPart
    )
in
    #"Added Custom"

     

     

    Put this in your Advanced Editor in PQ and see it work.