Creating Relationships based on Timestamp Range

[shanebo3239]

Greetings everyone,

I am having an issue that is probably more of just a data design issue than a PBI issue.  Either way, I cannot figure out how to proceed.

Our business has lobby customers that are queued into a lobby management (think "take a number") system.  This system has a table called "history".  The main fields of concern in the history table are:

ProcessID	StartDate	EndDate	CustomerServiceCounter
1	07/24/2017 8:30 am	07/24/2017 8:45 am	12
2	07/24/2017 8:37 am	07/24/2017 8:51 am	4
3	07/24/2017 8:42 am	07/24/2017 8:56 am	7
4	07/24/2017 8:46 am	07/24/2017 8:59 am	12

The second table is from a customer comment card system, which runs on a tablet at each customer service counter.  The relevant table here is "surveyResults" and the fields of concern are:

SurveyID	SubmittedDate	CustomerServiceCounter	Score
147	07/24/2017 8:42 am	12	5
148	07/24/2017 8:55 am	7	5

 Essentially, what I'm trying to do is join these two tables of data based on two factors:

1) Location where the survey was taken (CustomerServiceCounter) and

2) surveyResults.SubmittedDate falling between history.StartDate and history.EndDate

In other words, I should be able to get a table or set of data that looks like this:

ProcessID	StartDate	EndDate	CustomerServiceCounter	Score
1	07/24/2017 8:30 am	07/24/2017 8:45 am	12	5
2	07/24/2017 8:37 am	07/24/2017 8:51 am	4
3	07/24/2017 8:42 am	07/24/2017 8:56 am	7	5
4	07/24/2017 8:46 am	07/24/2017 8:59 am	12

 As you can see, every transaction will have a process id, a start date, an end date and a customer service counter.  But not every transaction will have a score.  Also, suffice it to say there's a lot of data in both of these tables that would be beneficial to join together that I'm not showing here for simplicity's sake.

I created a calculated column in the history table like the following, but no luck.  I thought if I could look up the SurveyId in the other table, I could then create a relationship between the two tables and get all of the other data I needed.  But, I just keep getting circular reference errors.  Can anyone help?

SurveyId = CALCULATE(MIN(SurveyResults[SurveyId]),FILTER(SurveyResults,SurveyResults[CustomerServiceCounter]='history'[CustomerServiceCounter] && 'history'[StartDate].[Date]<=SurveyResults[SubmittedDate].[Date] && 'history'[EndDate].[Date]>=SurveyResults[SubmittedDate].[Date]))

[shanebo3239]

Can anyone provide some guidance on this?  I've been trying LOOKUPVALUE() formulas today and still can't get the result I'm looking for.

[Anonymous]

I can think of a way to do this, but if and only if your submittedDate + CustomerServiceCounter columns (combined) in that survey results table is guaranteed to be unique.

You could expand out your time range into a duplicate row for every minute within the range, and then do an inner join to only take one of those duplicate rows for each survey result you have.

You could probably do this with a bit of M.