Help creating a relationship where the fact table record is related to an entry in the lookup table within a particular date range

[StrafeXL]

I really struggled with that title, I hope it made sense. I'm attempting to create a pivot table showing average survey scores over time by team and team member. What I'm having trouble with is how to deal with team members when they change teams. Unfortunately the system I use to retrieve survey scores is a 'black box' and the vendor wants to charge me multiple thousands of dollars just to add a team field to the report. So, I've been using index/match and a lookup table to assign team members to a team. The problem I have is, say a team member changes teams on 2/12. I can update the member's entry in the lookup table to the new team, however, this changes all survey's, not just the surveys since 2/12. I have been working around this, biding my time until my PC was recently upgraded to a machine capable of running PowerPivot in Excel 2013 no less! However, I'm still not sure how to accomplish this. My fact table looks similar to this (and I should mention, I'm simplifying this, there are also multiple surveys and the teams belong to different groups):

And my attempt at a lookup looks like the following but as you might guess, I can't create a relationship between the name fields due to many to many.

Ideally, I want to be able to do the following:

I've thought out several approaches, 1) creating a multi-column key on both tables (Name + Date), however, I imagine this wont work unless I have an entry in the lookup table with every combination of name and date. Doesn't seem practical. 2) creating an index/match in my source (an Excel table) and somehow matching the survey date to the corresponding effective date. I'd hate to do this at the source level because like I mentioned earlier, I'm trying to tie together multiple surveys/sources. Rob's book has been my bible and I'm wondering if a disconnected table might be the answer here but I just haven't been able to work it out yet.

Any suggestions would be greatly appreciated.

 

[miguel.escobar]

try this one
http://www.mrexcel.com/forum/powerp...-linked-table-info-used-calculate-column.html

 

[StrafeXL]

Thanks a lot for the suggestion. The situations do seem to be very similar. I think the difference is that my lookup (i.e banding) table does not have a unique key (i.e. more than one of the same name). I've tried it but I'm getting an error. Maybe I'm not understanding it completely. So, first I modified my lookup table, so there is a start and end date. I couldnt figure out the formula below with just the effective date.

My lookup table still has non unique entries (I'm using the name field to tie everything together) so I had to create a 'bridge' table because there has to be a relationship right? Otherwise how does the formula know what name I'm trying to look up:

So the survey table and the lookup table are related by the bridge table. Next I created a calculated field in the survey table using the formula mentioned in the link above:

=CALCULATE(VALUES(Lookup[Team]), FILTER(Lookup, Surveys[Survey Date] >=Lookup[Start Date] && Surveys[Survey Date] <= Lookup[End Date]))

but the column fills with #ERROR(s) and the message is

Calculation error in column 'Surveys'[]: A table of multiple values was supplied where a single value was expected.

I thought VALUES() was going to help me avoid that but there's something I'm just not getting. BTW, I tried to check out your solution in the dropbox link but the file seems to be damaged.

 

[miguel.escobar]

=CALCULATE(VALUES(Lookup[Team]), FILTER(Lookup, Surveys[Survey Date] >=Lookup[Start Date]), FILTER(Lookup, Surveys[Survey Date] <= Lookup[End Date])) )</pre>

Try this video also
The Powerpivot Way - Total All LOOKUPs - YouTube

 

[StrafeXL]

Well, I got it working. I had to modify the filter to explicitly filter the lookup table by the name associated with the record in the survey table:

=CALCULATE(VALUES(Lookup[Team]), FILTER(Lookup, Surveys[Survey Date] >=Lookup[Start Date] && Surveys[Survey Date] <= Lookup[End Date] && Surveys[Name] = Lookup[Name]))

I don't understand why, even though there was a relationship from the survey table to the bridge table and from the lookup table to the bridge table, the survey table and lookup table couldn't see each other. I'll have to experiment more.

 

[miguel.escobar]

sounded like a many to many case, I forgot to place that filter as you said but in that event what you could've done was use FILTER( RELATEDTABLE(Lookup), continue with the filtering process.... in that way, you're actually specifying that you're going to use the related table and go with the row context

 

[StrafeXL]

Thanks for the help. I will do that when I apply it to my real data.

 

[Laurent C]

Instead of using a calculated relationship, you can add an ID to your lookup table (calculated with concatenation, if need be) and add a calculated column to your Survey table containing that ID. Then use that key to relate both tables.

This is basically your idea 2), but done entirely within the model. (Also, the effective date cannot be a key for your table.)

I wrote a post about a VLOOKUP-like pattern in DAX (specifically for this kind of scenario):
Equivalent of VLOOKUP in DAX – Part II – using TOPN | The Data Specialist

 

[StrafeXL]

I'll investigate that as well, thanks!