none
Returning the 'nth' value from the 'many' table to the 'one' table

    Question

  • 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


    Tuesday, March 27, 2012 12:36 PM

Answers

  • " 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.

    Tuesday, March 27, 2012 5:37 PM

All replies

  • " 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.

    Tuesday, March 27, 2012 5:37 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/

    Wednesday, March 28, 2012 8:25 PM
    Answerer