DAX and Time Windows

[PBCIT]

Hello all,

I'm a little stumped at the moment and not sure exactly the best way to go about solving my problem. I have two tables I pulled from a database, one that contains time windows for a customer and another that has delivery times in it. There was no natural connector between the table, so I concatenated the customer number and the day number (ex. customer 123 and Friday would be 123-6). Then I did the same on the delivery table so that the customer and day of delivery would check the time windows for the customer and what the time windows should have been. It worked great except for one problem.... some of the customers have multiple time windows on the same day. I'm stumped. Does anyone have any ideas for how I can differentiate the time windows in both tables? For instance, I keep thinking there has got to be a way that I could maybe create and concatenate a sequence into the mergeID I created and then depending on the time in the delivery table, pull the correct mergeID? Or is there some other solution that I could utilize?

Any help would be GREATLY appreciated.

[AlB]

Hi @PBCIT 

Have you considered CONCATENATEX( )

It would help if you share some sample data and the expected result

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[PBCIT]

I'm not understanding where this would help. I had another thought. I connected the customer ID in the time windows table to the customer table I have and the customer table is connected by customer ID to the delivery table. Do you know of a way where, if the account ID in the delivery table matches the account ID in the Time windows table, it would take the arrival time and find the one that is closest to the arrival time to compare it against? Previously, what I had done was I created a column where it concatenated the customer ID and the date. Then I created a column that flagged if I had multiples for a day. Then if the sum of the amount of times a delivery was out of the window was <=1, then I replaced it with a 0  because we did actually deliver within a time window and I don't want it to count as having not made it in the time window. This solution works but I just keep thinking there has got to be a more elegant way to go about this, ya know?