Table relationship giving the wrong results

[markfay]

Can someone help me please, I simply cannot work out why my PP relationship will not work as expected.

I have two tables linked into power pivot. The first (table 1) is the Customer site ID and the Customer satisfaction data. Each site ID is unique.
The second is a complete list of all site ID's (whether appearing in the first table or not) and the corresponding site name, country etc (Table 2)
I have created a relationship from table 2 as the dimension lookup to table one, which I see as the fact table.

In the pivot table report I simply want to list the site id from table 1 and pull in the country from table 2. However when I do this I end up with the site id listing every single country rather than just the one country.

I've read lots of tip's training etc but cannot understand why this happens. Can anybody explain in simple language what's happening. I also note that auto relationship detection won't work either which is perhaps related.

thanks

 

[Tycho01]

Hi, could you post the DAX query you used in PowerPivot? I've managed to do succesful lookups using RELATED, USERELATIONSHIP, and LOOKUPVALUE. Google up their respective MSDN articles for details.

 

[markfay]

Hi, could you post the DAX query you used in PowerPivot? I've managed to do succesful lookups using RELATED, USERELATIONSHIP, and LOOKUPVALUE. Google up their respective MSDN articles for details.

thank Tycho, I didn't use any DAX formulas though, I established the relationship between the two tables and then created a pivot table showing the Customer site ID from the first table and the country from the other. It was then that the result came up as the site id and a list of every country.

 

[Tycho01]

Ah, I see what you mean now. I haven't tried pivot tables from PowerPivot much yet, but I get your issue now (as in replicated). I'm not sure about the proper way to get pivot tables to 'get' the table relations, though if getting a quick merged table view is what you wanted, adding a column in the main table in PowerPivot using the RELATED() DAX formula may help you as well. If you wanted more, I'd be happy to see others' replies as well.

 

[markfay]

Ah, I see what you mean now. I haven't tried pivot tables from PowerPivot much yet, but I get your issue now (as in replicated). I'm not sure about the proper way to get pivot tables to 'get' the table relations, though if getting a quick merged table view is what you wanted, adding a column in the main table in PowerPivot using the RELATED() DAX formula may help you as well. If you wanted more, I'd be happy to see others' replies as well.

Thanks Tycho. I'll be exploring the DAX formulas. The issue is our data models often use multiple tables and vlookups and rather than build a flat file equivalent in powerpivot I felt using the pivot report and simply pulling in the data from different sheets would enable us to respond to Client requests much faster.

I know it is possible as I've seen it work with sales figures but cannot figure out what it is that's wrong when I try it.

Mark