Powerpivot: "the relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique"

[hakanfa]

Hi,
I have a problem getting two tables to work together despite unique relationship. The table 3 has a column (date) which is unique. The Table 1 and 2 have also dates but not unique.
When building the relations between these it works fine, but when i excel trying to use these data in a pivot table I get the error as mentioned in the header.

What am I doing wrong?

Pivot structure:
Values: Sum of NetRev
Rows: Date (from Table 3)
Filter: Sales period (from Table 2)


Table 1
Col1 Date (many dublicates.. this is sales date)
Col2 Product
Col3 Sales rev

Table 2
Col1 Date (many dublicates)
Col2 Sales period (YTD, QTD or MTD)

Table 3 (Calendar table)
Col1 Date (unique)
Col2 Month
Col3 ..


Bes regards,
Hakan

 

[hakanfa]

Sorry - I'm totally lost. If you have the time - please look at this Excel .. cannot get the measures and the set-up to work as expected.

Dropbox

www.dropbox.com

This is a "clean" set-up with no measure or linking. Only 3 tables, one with "data" Date, Net Rev. Weight and Units, Table 2 with the selections YTD, QTD and MTD and a Calendar table

 

[RoryA]

This is what I was thinking of: YTD_DemoX.xlsx

 

[hakanfa]

Wow - this was great solution! Coming back to the measures issue i mentioned earlier. Do you think it would be possible to implement the same logic for measures? That is having a separate table and slicer for selecting measures? This would solve all my problems! I added a independent table to the file but totally locked how the logic would work .. if even possible..

Dropbox

www.dropbox.com

 

[RoryA]

It would be possible, but would become unwieldy very quickly, since you will have to have nested SWITCH functions to cover all the permutations.

 

[hakanfa]

It would be possible, but would become unwieldy very quickly, since you will have to have nested SWITCH functions to cover all the permutations.

Hmm.. maybe yes but what would be the best practice to cope with different measures? Tricky one this issue..

 

[RoryA]

I don't know what best practice is. Typically I would have different visuals/pivots for different measures, or allow the user to drag/drop whatever they need.

 

[hakanfa]

Well yes, but coming back to nested SWITCH function - would that be putting IF statements inside the SWITCH statement or how would it work?

 

[RoryA]

No, you'd put one switch inside another. You'd still need to create all the measures though.

 

[hakanfa]

No, you'd put one switch inside another. You'd still need to create all the measures though.

Hi Rory and a BIG thank you for the support and pointing me in the right direction. I managed to implement the “nested switch” function and got it working perfectly! Finally I got this working as expected! Thank you!

 

[RoryA]

Glad we could help.