Find the next nearest value with filtering

enieuwer — Wed, 14 Jul 2021 10:11:16 GMT

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!

Re: Find the next nearest value with filtering

daxer-almighty — Wed, 14 Jul 2021 13:35:17 GMT

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.

topic Find the next nearest value with filtering in DAX Commands and Tips

Find the next nearest value with filtering

Re: Find the next nearest value with filtering