Forum Discussion
looking up values where two ranges apply
Hello, I have a customer table with multiple status that relate to a date range. I've used these date ranges to create and a unique reference for each customer&status. i.e Customer - A, Status ...
Another option is to start with the Orders table, merge in the Customer table (matching on the Customer column), expand the date and unique reference columns, and then filter out the rows that don't satisfy the time overlap.
Here's some example code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUbK0MDcFUib6hvpGBkZGQKYZjBmrA1ViaWgOpMz1TWBKTPVNoUpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Order = _t, #"Start Date" = _t, #"Order End" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Order", Int64.Type}, {"Start Date", type date}, {"Order End", type date}}, "en-IN"), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Customer"}, Customers, {"Customer"}, "Customers", JoinKind.LeftOuter), #"Expanded Customers" = Table.ExpandTableColumn(#"Merged Queries", "Customers", {"Start Date", "End Date", "Unique Reference"}, {"Customers.Start Date", "Customers.End Date", "Customers.Unique Reference"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Customers", each ([Customers.Start Date] <= [Order End] and [Customers.End Date] >= [Start Date])) in #"Filtered Rows"
Hi Anonymous ,
I would advise doing this in DAX. Power Query can do this, but performance will be awful compared to DAX.
In DAX you would create a new calculated column in your Orders table, something like this:
..customerUniqueReference =
CALCULATE(
VAR __customer = VALUES(ordersTable[Customer])
VAR __orderStart = VALUES(ordersTable[StartDate])
VAR __orderEnd = VALUES(ordersTable[EndDate])
RETURN
MAXX(
FILTER(
customersTable,
customersTable[Customer] = __customer
&& customersTable[StartDate] <= __orderEnd
&& customersTable[EndDate] >= __orderStart
),
customersTable[UniqueReference]
)
)
You may want to fiddle wth the '>=' and '<=' bits to get the exact matching behaviour that you need, but this basic structure used in DAX should be super-fast.
Pete
Thank you for your reply, its good to know there are many ways to achieve the desired result.