Results 1 to 5 of 5

Thread: Relationship links
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Relationship links

    I have read many posts from different sites and want to make sure I am understanding the limitations (IMHO) of PowerPivots

    What I want to do - I do quite easily in both Access and Crystal Reports but thought I would see how PowerPivot works

    I have 2 separate excel files

    1 files has a list of students with their home addresses and includes their student ID # (Unique) = Contact File
    NAME/Address/Student ID

    other file is same list of students/their student IDS/and a Absent field indicating how many times they have been absent= Absent file
    NAME/Student ID/Total Absents

    The end report should be a list of students /with their address/total absents = 3 columns
    NAME/Address/Total Absent

    In Access and crystal I simply link the student iID fields and run the report- works every time
    PowerPivot no combination of links seems to work, given the lookup relationship and the absent field valculated - you guessed it the total for all students not the row record

    I read that I can create a DAX calculation but no way my users are going for that I much rather take it back to access or crystal

    So am I to understand that linking the tables in PowePivot is not really linking like in Access but , yes an easier vlookup, and really does not join the data on the unique field

    Many Thanks

  2. #2
    Board Regular Jacob Barnett's Avatar
    Join Date
    Dec 2012
    Location
    Manchester, UK
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Relationship links

    Lyambor, PowerPivot does have limitations in terms of relationships but this isn't one of them!

    Basically you got your relationship the wrong way round - the many has to be on 'the top'. I'm guessing your 'many' table (absences) has one row per student meaning that PowerPivot couldn't determine which was which. Had this been a more standard fact table with multiple records per student then it would have automatically corrected your mistake.

    I created a 2 minute example to prove the point: http://sdrv.ms/Ylu3SC

    HTH

    Jacob

  3. #3
    Board Regular Jacob Barnett's Avatar
    Join Date
    Dec 2012
    Location
    Manchester, UK
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Relationship links

    Not sure Skydrive will let you just download. Try: https://docs.google.com/file/d/0Bz5y...it?usp=sharing

  4. #4
    New Member
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Relationship links

    Quote Originally Posted by Jacob Barnett View Post
    Not sure Skydrive will let you just download. Try: https://docs.google.com/file/d/0Bz5y...it?usp=sharing
    Jacob-

    Thank you for you quick response and sample file

    Your sample looks similar to mine where you only have the student listed once in each file

    my contact file is a list of every student in the school including the address
    my absent file is a list of each student with their total absences no address field

    I tried putting the absences on top for the relationship in PowerPivot
    but I got an error message "Relationship could not be created in the requested direction. When you click link the relationship will be reversed"

    I understand the one-to-many relationship but essentially this is a one to one relationship
    simply match up the student numbers so I can report out the address

    Yours seems to be doing what I need with exactly the same kind of data -- student names listed only once in each file

    ABSENT

    Chisarick Mike 485563 2
    Clark Megan 577501 2
    Connelly Nan 607400 1.5
    Davis Pete 662751 3
    DelPo Joseph 479737 3

    CONTACT
    Chisarick Mike *485563 101 Main Street
    Clark Megan 577501 212 Cherry lane
    Connelly Nan *607400 86 Canden Way
    Davis Pete * 662751 1 Springtime View
    DelPo Joseph 479737 12 Curry St


    What am I missing?
    Thank you for your patience

  5. #5
    New Member
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Relationship links

    Quote Originally Posted by Jacob Barnett View Post
    Not sure Skydrive will let you just download. Try: https://docs.google.com/file/d/0Bz5y...it?usp=sharing
    Jacob,

    I mocked up a similar 2 tables with my data and it worked fine just like yours
    but with the actual state data files my relationship did not work because there really was no true lookup table PowerPivot was looking for as you said

    But I did a DAX calculation in the address table to get the absent data powerpivot could not link to by student-which worked like a charm:

    =CALCULATE(SUM(absent[Total Absences]))

    When I inserted a new column in the table that PowerPivot looks to as the lookup table so to speak
    the absenses display correctly instead of a total of the file and I can then easily pivot on the data exactly as needed

    A really helpful site on PowerPivot relationships can be found at:
    Working with relationships in PowerPivot & SSAS BISM Tabular | Javier Guillén


    many thanks for your time and effort and getting me on track
    Thought I would share
    Cheers

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •