Returning the 'nth' value from the 'many' table to the 'one' table
-
Tuesday, March 27, 2012 12:36 PM
Hi,
I've got 3 PowerPivot tables that have a relationship on 'email': a list of unique email addresses, the 'customer' table from the database and a data dump from our outgoing EDM provider.
The task in hand is to produce a single customer 'table' with one record per customer with the difficult point being that both sources tables feature thousands of duplicates and thousands of emails that aren't on the other!
I've begun by manually creating a complete list of unique emails that I have used to form relationships with the other two tables and I'm now looking to start bringing data from the 2 'many' tables into calculated columns in the 'one' table.
The catch is that I can't work out the DAX, even to do a basic 'Vlookup' style function - pretty sure its going to involve EARLIEST but I can't quite get it to work.
And while returing the first instance of a text value in the related table would be a start, being able to stipulate it being the 'nth' item would be very useful.
Does anybody know how i can achieve this?
Regards
Jacob
All Replies
-
Tuesday, March 27, 2012 5:37 PM
" I'm now looking to start bringing data from the 2 'many' tables into calculated columns in the 'one' table."
I'm not sure what your final goal is, but in PowerPivot it is totally unnecessary to bring in data from other tables.
Use the RELATED function if you have to, however. It is the PowerPivot equivalent of the VLOOKUP function.
- Marked As Answer by Challen FuModerator Wednesday, April 04, 2012 9:20 AM
-
Wednesday, March 28, 2012 8:25 PM
Jacob,
One thing makes me curious.... if your goal is to bring data from the 'many' side to the 'one' side (or table), then how would you have situations in which your 'one' side has multiple (and different) records on the many side? or are just trying to consolidate the email field and nothing else?
Javier Guillen
http://javierguillen.wordpress.com/
.png)
