Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]))
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |