none
Trying to build a relationship between two tables

    Question

  • So I have two sets of data that I have pulled from different reports that I am trying to combine into one pivot table.

    The first contains: Part Code, Part Description, Customer Number, Customer Name, Sales in lbs

    The second contains: Part Code, Customer Number, and Specification

    I have built a pivot table that lists which customers buy the most of each product, by sales lbs. There are 36 Part Codes, each one with ~20 customers that buy that product. There is a lot of customer overlap, and by that I mean that each customer likely buys 3-10 different products from us.

    The second table I have lists the customer number (an internal ID) next to the product, with the Specification that we use for that customer, for that product. By this I mean that each customer might want a slightly different set of test specifications than the next for the same product. All I need from this table is the specification name that is used for that product, for that customer.

    When I try to link the two tables, I end up getting the error that there are duplicates in both tables. I understand that there needs to be unique identifiers in one of the two tables, however because customers buy multiple products they would be listed under two different products, thus being seen as a multiple. Each customer is in fact unique within that product, but because they are listed one after the other, they are seen as duplicate. Is there any way to link the Part Code and Customer Number together, then use the new linked identities to make a relationship.

    Is there any way to build the first table, but include the Specification column also?

    Friday, June 06, 2014 11:02 PM

Answers

  • Tim, there are a couple of ways to look at this:

    • If each customer has a single specification for each product then you could handle this problem by creating a Part Code-Customer column on both tables. This would work because the second table would then have a unique column.
    • If all you want to do is bring in the specification from table 2 to table 1 then you can write a DAX formula in a calculated column without the two tables actually being related. Something like this could work:

    =
    CALCULATE (
        MAX ( Table2[Specification] ),
        FILTER (
            Table2,
            Table2[Part] = EARLIER ( Table1[Part] )
                && Table2[Customer] = EARLIER ( Table1[Customer] )
        )
    )

    This assumes that the specification is numeric, if not VALUES() might work. It becomes more challenging if each customer has more than one specification per product in that you will have to devise a way to choose which one - MAX() would do that for you in numeric terms but something like TOPN() would be required for text although you would still need a numeric way of choosing e.g. Date.


    Jacob | Please mark helpful posts and answers


    Saturday, June 07, 2014 8:06 AM

All replies

  • Tim, there are a couple of ways to look at this:

    • If each customer has a single specification for each product then you could handle this problem by creating a Part Code-Customer column on both tables. This would work because the second table would then have a unique column.
    • If all you want to do is bring in the specification from table 2 to table 1 then you can write a DAX formula in a calculated column without the two tables actually being related. Something like this could work:

    =
    CALCULATE (
        MAX ( Table2[Specification] ),
        FILTER (
            Table2,
            Table2[Part] = EARLIER ( Table1[Part] )
                && Table2[Customer] = EARLIER ( Table1[Customer] )
        )
    )

    This assumes that the specification is numeric, if not VALUES() might work. It becomes more challenging if each customer has more than one specification per product in that you will have to devise a way to choose which one - MAX() would do that for you in numeric terms but something like TOPN() would be required for text although you would still need a numeric way of choosing e.g. Date.


    Jacob | Please mark helpful posts and answers


    Saturday, June 07, 2014 8:06 AM
  • Hi Tim,

    If I've understood you correctly, you could also try using the DAX LOOKUP function to add a calculated column to table 1 which pulls the Specification value from table 2:

    =LOOKUPVALUE(
      Table2[Specification], 
      Table2[Part Code], Table1[Part Code], 
      Table2[Customer Number], Table1[Customer Number]
    )

    undefined


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn


    Saturday, June 07, 2014 2:48 PM
    Moderator