topic Re: Trying to Create new Column/measure thats matched on ID and a Date Range in DAX Commands and Tips

Trying to Create new Column/measure thats matched on ID and a Date Range

Anonymous — Fri, 03 Dec 2021 22:51:09 GMT

The three tables I have are shown below

Customer Historty Table

ID	CustomerID	PostedDate	Old Address	New Address
951753	879564213	1/10/2021	123 N Way	789 S Broadway
753951	879564213	1/01/2021	Null	123 N Way
357159	231564879	12/31/2021	Null	456 E Central

Customer Table

ID (CustomerID)	ReportedDate	Current Address
879564213	1/10/2021	789 S Broadway
231564879	12/31/2021	456 E Central

Order Table

OrderID	CustomerID	SurveyDate
987564213	879564213	1/12/2021
321654987	879564213	1/12/2021

Currently if I wanted to pull order information for OrderID 987564213 it would return multiple address or just the current one. If I only tied it to the Customer Table then I would only get the most current address, not necessarily the one the customer lived at when they made the order. But if I tie it to the Customer Historty Table then I would get multiple address for the order with no way of knowing which one was the ACTUAL address at the time of the order.

What I would like is either a new column in the Customer Historty Table that would expand the table to include every single day and put the respective customer address for each day. Or a mesure in the Order Table that could do something similar, by using the order date to determine which Address to use if its after or before they changed address.

What I have tried is a mesure that checks the order date and compares it to the latest PostedDate in the customer history table. This did not work as some customers have moved many times a few years so using the latest date wouldnt work.

Re: Trying to Create new Column/measure thats matched on ID and a Date Range

amitchandak — Sat, 04 Dec 2021 11:58:18 GMT

Anonymous, You can add a new column in order and can connect directly with Customer History Table

Join ID =
var _max = maxx(filter('Customer Historty Table','Customer Historty Table'[PostedDate] <='Order'[SurveyDate]
&& 'Customer Historty Table'[CustomerID] <='Order'[CustomerID] ),'Customer Historty Table'[PostedDate])
return
maxx(filter('Customer Historty Table','Customer Historty Table'[PostedDate] =_max
&& 'Customer Historty Table'[CustomerID] <='Order'[CustomerID] ),'Customer Historty Table'[ID])

Re: Trying to Create new Column/measure thats matched on ID and a Date Range

ValtteriN — Sat, 04 Dec 2021 12:11:30 GMT

Hi NotRick,

I am not sure if I understood correctly but in essence: you want to get the NewAddress information for a order during the time of order (SurveyDate)?

This can be achieved with the following measure:

Addres during the time of order =

var orderDate = MAX(OrderTable[SurveyDate]) return

CALCULATE(MAX(CustomerHistory[New Address]),CustomerHistory[PostedDate]<=orderDate)

While using this measure in the order table the end results looks as follows:

Hopefully this solves your problem.

Re: Trying to Create new Column/measure thats matched on ID and a Date Range

Anonymous — Mon, 06 Dec 2021 16:25:26 GMT

ValtteriN Thanks for the response, Im having trouble understadning how this would work? So if im inderstanding the DAX correctly it is making a variable for the date of the order. Then returing the value in the new_address coloumn if posted date is less than or equal to the variable orderdate. How does that return the correct address if 9/01/2021 (orderDate) is greather than the last posted date? Also what does the MAX function do here? Thanks

Re: Trying to Create new Column/measure thats matched on ID and a Date Range

ValtteriN — Mon, 06 Dec 2021 16:38:19 GMT

Hi, The MAX is used on the variable to select a single value. Without MAX of something similar like selectedvalue The formula doesn't work. The Logic behind The posted date is as follows: The way I understood your question is that 'customer history' contains the date when a customers address changed [posted date]. If this posted date is lower than the order date DAX selects The MAX case our of this data so the latest row where the customer's address changed. Does this clarify the DAX?

Re: Trying to Create new Column/measure thats matched on ID and a Date Range

Anonymous — Mon, 06 Dec 2021 16:54:25 GMT

ValtteriN
Yes postedDate is when a customers address was changed. I just asking because I am trying to understand why its not working. I am looking at one customer and all address returned ,total of 5, are the same and its returning the most recent address not the one at the time of the order. Would that fact the relationship is many to many break the DAX?

Re: Trying to Create new Column/measure thats matched on ID and a Date Range

ValtteriN — Mon, 06 Dec 2021 18:57:26 GMT

Yeah, like you suggested m:n relationship will cause issues. This is the data model I used in my example:

Is it possible to modify your model to mirror this? (I used CustomerID as the key)

Re: Trying to Create new Column/measure thats matched on ID and a Date Range

Anonymous — Mon, 06 Dec 2021 20:04:51 GMT

I changed the model to look like that, and now its returning the same address for all rows of that customer, and wierdest part its not even an address thats associated with that customer.

Re: Trying to Create new Column/measure thats matched on ID and a Date Range

ValtteriN — Mon, 06 Dec 2021 20:50:37 GMT

Hi,

That sounds quite confusing. My first instinct is that there is a problem with relationships or that the column you are using in the visulization causes the issue. Typically when a measure returns the same value for all the rows there is a issue with the relationship this is especially true if the row is duplicated or the returned value doesn't make any sense. In this case since the measure I used in my example returns values that make sense I am leaning even more towards this.

Usually in this situation I double the data intregity of my key columns. So my question at this point is are you sure the data type is the same with your ID columns and have you done "clean" and "trim" steps in powerquery?