Relationship links

[lyambor]

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

 

[Jacob Barnett]

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

 

[Jacob Barnett]

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

 

[lyambor]

Not sure Skydrive will let you just download. Try: https://docs.google.com/file/d/0Bz5yMU2oooW2bXI4dlRGdDBGbVk/edit?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

 

[lyambor]

Not sure Skydrive will let you just download. Try: https://docs.google.com/file/d/0Bz5yMU2oooW2bXI4dlRGdDBGbVk/edit?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