Relationship May be Needed

[bartmanekul]

Hi,

I've done an awful lot of reading and whatnot over the past week, and cannot solve this problem.

I'm trying to do something I thought simple: Get a simple list of special prices for product, by customer account number.

This isn't massive quantities, around 8000 products and around 3000 accounts.

Currently, I'm trying to do a pivot table which SHOULD be able to do this. However, I get the 'Relationship May be Needed' message even though they are correctly linked. Research shows this is likely due to them being an integer, so somehow breaks the link even though when I check it's still there and correct. Of course, as it's Sage I can't change the data source.

A workaround says to create a calculated field, and use the 'REFERENCE' term.

But I can't do this, because it won't let me add a calculated field.

I can't add a calculated field because I can't detick 'add to data model'.

I can't detick add to data model because of the data source (OLAP)

AAARRRRRRRRGGGGGGGGHHH.

What I want is pretty simple, is there any workaround for this? Note that it needs to be able to be refreshed often so needs to be a working fix, not a single one if that makes sense.

I'm using Sage 200, office 365.

Visual of what I want:

Item code	Customer 1	Customer 2	Customer 3	Customer 4
Item 1	5.99	3.58	3.89	8.14
Item 2	4.88	4.99	5.22	4.77
Item 3	3.21	3.54	3.68	3.10

 

[RoryA]

In Power Pivot a calculated field is a measure. Can you not right click the table in the field list and add a new measure? (I'm assuming you have Power Pivot loaded?)

 

[bartmanekul]

Thanks for the reply, sadly it still sees it as a broken link (I've gone into relationships and tried to make it, but it already exists). I'm absolutely stuck on what seems to be something so simple.

 

[RoryA]

Can you post a file somewhere (onedrive / dropbox etc) for me to have a look at?

 

[bartmanekul]

Of course, I'm grateful for any help. What exactly would you want to look at, as I can't desensitise the source due to the quantity? Just an example of what's needed?

 

[RoryA]

I'd need something that replicates your actual error. Only a few lines of data in each table, suitably anonymised, as long as it still shows the same issue as the real data set.

 

[bartmanekul]

I don't think that's possible without the actual connection - but I can try anyway.

The problem seems to be this: “Relationship may be needed?” But I already have a relationship! What’s going on? - PowerPivotPro

But the work arounds in there don't work. I'll try to replicate in a smaller file.

 

[bartmanekul]

Unfortunately this isn't replicable without a connection to a data source.

The problem is, when a field as integer is added as a value, it simply doesn't see it - it says there's no link even when there is, and all results come back as the same one.

This is a frustrating bit of excel which doesn't seem fixable - I'm trying other methods but even the pivot function of SQL isn't an option with many columns.