Error on Relatioships between tables

[jazzista]

Hello. I have created a relation between 2 tables but power pivot still cannot recognize the relationship. I am getting the same aggregation result in all the rows ( $ 70,389.67 in all the lines) The message reads : RELATIONSHIPS BETWEEN TABLES MAY BE NEEDED. I have deleted the dimensional table 2 times and re-added to the model but still cannot get it to work. Please help! I don't know how to paste the file in the thread.


Row Labels Double Payment August 2014 Net Remaining Balance
GOLDEN B UNIT 1H 14,619.71 -70,389.67
GOLDEN UNIT B 2H 12,446.94 -70,389.67
GOLDEN UNIT B 3H 16,175.56 -70,389.67
GOLDEN UNIT B 4H 636.24 -70,389.67
GOLDEN UNIT B 5H 16,185.42 -70,389.67
GOLDEN UNIT B 6H 4,519.09 -70,389.67
GOLDEN UNIT B 7H 11,820.11 -70,389.67
GOLDEN UNIT B 8H 5,375.87 -70,389.67
GOLDEN UNIT B 9H 18,642.96 -70,389.67
GOLDEN UNIT B 10H 13,792.92 -70,389.67
GOLDEN UNIT B 11H 18,633.53 -70,389.67
BERDIE UNIT 1H -73.97 -70,389.67
BERDIE UNIT 2H -68.36 -70,389.67
Grand Total 132,706.02 -70,389.67

 

[esadovnic]

Do you have a dropbox account? You can submit your doc in a link from there (Share option). I'd like to take a look at it to figure out where you're running into the issue.

 

[jazzista]

Esadovnic: How are you? See attached file. Thanks in advance for your help.

https://www.dropbox.com/s/g1o0wf96riqqx4y/Encana double payment analysys.xlsx?dl=0

 

[esadovnic]

Hey I'm doing well thanks for asking! How's everything with you?

There are a couple of points in contention here:

1. The main issue is that your look up table Tblwells does not match the PropertyName from both tables. It only matches the doublepay table, which means that you need to create a uniform naming convention in order for this calculation to work.
2. When dealing with the now classic M2M problem you should use the new lookup table [Wells] field instead of the PropertyName fields from either table.
3. Instead of using SUM() use =CALCULATE(SUM([Net]), 'Tblwells')

Let me know if these changes help you solve this issue.

 

[jazzista]

Esadovnic. Good morning and Thanks for your reply. So there doublepay table does not match the Tbwells table. Interesting. That's why I cannot detected the relationship. I get that part. So why the model detects the relationship of the tables in the diagram view? I guess, I was fooled by that. I while update everything and will let you know. Last question and again I am trying to get my head around power pivot: why use the Sum function wrapped in the calculate function? Again, Thanks in advance for your help.

 

[esadovnic]

I ran into the same problem. When I wrote the =CALC... measure, I noticed only a few lines of Properties were registering [Net] amounts. Then some digging around and removing duplicates revealed mismatched names. I'm not totally sure why it detects a relationship, but obviously a few records match and they meet the "unique" field names criteria required for relationships.

Power Pivot is sometimes black magic. The =CALCULATE() function is one of those magic formulas that enforces a new filter context. If you noticed the second input references the table by itself, which 'connects' the two tables via filter context. I can't pretend to understand it 100% but I don't feel too bad because it's considered unusual due to the comments on Rob's post (link above).