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

 

[RoryA]

What date field are you putting in the pivot table - the one from the calendar table, or one of the others?

 

[hakanfa]

What date field are you putting in the pivot table - the one from the calendar table, or one of the others?

The one from the Calendar table..

 

[RoryA]

I don't understand your table2 - what is it for?

 

[hakanfa]

I don't understand your table2 - what is it for?

Hi, I have for a long time tried to figure out how to get YTD, QTD and MTD to slicer. In my application I already have monthly slicer, weekly slicer but I also need the YTD, QTD and MTD. These segments I did manually (what dates belongs to which segment) ex. 1.1.2021 - 30.11.2021 YTD, 1.10.2021 - 30.11.2021 QTD and finally 1.11.2021 - 30.11.2021 MTD. This means that I have several duplicate date rows but with sales period. Screenshot of set-up attached

 

[RoryA]

I think you'd be better off creating measures for each calculation, then switching between them using a disconnected table and a measure like:

PeriodToDate =
IF(HASONEFILTER(Table[CalcType]),
SWITCH(SELECTEDVALUE(Table[CalcType]),
"MTD", [Measure 1 MTD],
"QTD", [Measure 1 QTD],
"YTD", [Measure 1 YTD]
),
BLANK()
)

to switch between them based on your slicer.

 

[hakanfa]

I think you'd be better off creating measures for each calculation, then switching between them using a disconnected table and a measure like:

PeriodToDate =
IF(HASONEFILTER(Table[CalcType]),
SWITCH(SELECTEDVALUE(Table[CalcType]),
"MTD", [Measure 1 MTD],
"QTD", [Measure 1 QTD],
"YTD", [Measure 1 YTD]
),
BLANK()
)

to switch between them based on your slicer.

Hi, sounds good but now I need some more information - the PeriodToDate is a measure related to what table? Is the Table[CalcType] a new table and with what structure and data?

 

[RoryA]

You need to create three measures - one for YTD, one for MTD and one for QTD. Then you have a separate table that has MTD, YTD and QTD as its three values. This new fourth measure will then pick the appropriate measure to return based on the selected value from the new table. All the measures belong to Table1 logically.

 

[hakanfa]

Hi, good progress but one issue still - using measure like MTD:= IF(Date >= MONH(TODAY()); SUM(Net.Rev);0) - this will work as such but.. at this stage the Table 1 has only one column with sales data - the idea is to add several columns wit different kind of data like "sold topics", "topic segment" etc. Then I will end up doing measures for all different columns.. right?

 

[RoryA]

Yes, you will need appropriate measures for each value. You should be using time intelligence functions like TOTALMTD, TOTALYTD and TOTALQTD.