Relationship problem with VERY simple tables!

Question

I am admittedly a newbie, that being said, I can't possibly figure out what I could be doing wrong.  I found a very similar issue in the forums and it seemed like it might be a bug- if so, has it not been resolved?  See that thread here:  http://social.msdn.microsoft.com/Forums/en-IE/sqlkjpowerpivotforexcel/thread/dd7adeb2-80c1-4bbd-9745-d3a83a44201b

Here's the basic view of what I have.  Please for the life of me tell me what I'm doing wrong and why it's cross joining everything:

 

 

Answer 1

Hi,

In table1 you need to create the following calculated columns to refer to the columns in table2.

=RELATED(Table2[First Name])

=RELATED(Table2[Last Name])

=RELATED(Table2[Email])

Then you can hide all the columns in Table2. 

Kind regards,

Frederik

 

 

Answer 2

Thank you for your reply.  I have tried this, but for some reason I'm getting a syntax error when I do this.  I've tried it without the quotes as well but that doesn't work.  Let me know if I'm doing something wrong.  Is this a bug??? Should I have to create calculated columns for this to work????

---

Leslie M Waters

Answer 3

Hi Leslie,

Try using CALCULATE instead of RELATED (this, provided you one have one entry for [last_na] on the other table).   Check points # 2 and #5 here: 

http://javierguillen.wordpress.com/tag/tabular-relationships/

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 4

Leslie,

What's the exact error message?  Did you create a relationship from table1 to table2?

Answer 5

Javier-

I did try doing a random "calculate" to see if that would help, but unless I'm doing something wrong (which is entirely possible), it still didn't work.  See my results below.  ATTENTION MICROSOFT-  Is it not a little excessive that I should have to go through all of these crazy measures just to achieve a very simple join between two very simple tables?? Am I missing something here?

---

Leslie M Waters

Answer 6

Hi Leslie

Can you give me a small example of the data and what exactly are you trying to get out of the calculation?  A small (10 row or so) example of each table would be enough to understand what you are trying to do. I know you gave a sample at the top, but that seems to be different from this wv_idt_corp_personnel_basic table

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 7

Leslie,

The key thing to keep in mind is that the propagation of context filters between related tables only works in one direction. The filters or context of a dimension/lookup table will be applied to the fact table but the filters or context of a fact table will not be applied to the dimension/lookup table. That explains what you're seeing in your pivot table.