PowePivot will not create a relationship from my date table to fact table

[geode]

Hi Guys - Newbie here. I want to preface this by saying that I did attempt to find the answer on my own before I reached out for help but I was unable to find the solution to my issue.

I created a date table to use to link to my fact tables. When I attempt to create the relationship it won't let me because it is saying that both columns have duplicate values. I know this isn't true in regards to my date table "primary key" column because I've checked and rechecked this column by attempting to remove duplicates with each time excel saying "no dupes found".

I thought I had located the problem at one point because the column was formatted with a preceding asterisk date (the dates that change by the day, etc.). But even when I re-formatted it that still didn't solve the problem.

What am I missing??? I'm about to pull my hair out!

**I created this date table on my own since I couldn't find one that was easily downloadable. Microsoft has one supposedly in a "Contoso" sample workbook but it is off of 2010 and I can't open the managed tables since I'm working off of 2013. There was another one on some site but it was get downloaded in the format I needed. It was in some sort of "feed" - way out of the scope of my knowledge.
**Also, I did start with 1/1/1900 which shouldn't have made a difference but thought I'd put that out there. I just typed in the first date and copied/dragged down the rest.

Thanks for your help! Any assistance would be most appreciated.

 

[geode]

Excuse the typos above.

Should I have given my date table a primary key column? Would that help? I figured since the dates are unique it could be its own primary key column. Also, In addition to the date key, there are other columns as well (month key, day key, year key, month/year, day of the week, and calendar qtr/yr).

Thanks again.

 

[geode]

**UPDATE:

I think I may have found the answer. When I initially brought in the date table, I would only select the sheet that had the dates in it and not the other sheet, which only had the column names included. This time, I brought in both and linked that "empty" sheet to the fact table. Then when I performed some pivot maneuvers, I was able to actually use the columns from the "real" date table and it is working so far.

The only thing that is causing me pause this time (which was happening before) is that when I try and mark the the date table as well...the date table, I don't get any kind of dialog box that lets me know this has in fact happened. Now that I've pulled in that empty sheet the "Mark as Date table" grays totally out when trying to use this empty table so I again marked the other one as the date table even though I'm unsure if it actually did anything since nothing happens when I click it. Anyhoo, just wanted to give an update.

 

[geode]

NO I WAS WRONG THAT DIDN"T WORK

 

[erinward11]

Just an idea but create a primary key, I use 10000*Year(Date) + 100*Month(Date) + Day(Date) then create a pivot table off your table in excel... Drag the Primary Key into the rows and then into values section as a count. Sort Desc and see if there are any duplicates or blanks.

 

[geode]

Thanks I will try right now and let you know. Question: Once I've created that column and did the spot checking with the pivot table, do I keep that as my primary column and do I use that column to create the relationships AND to recognize it as the date table?

 

[geode]

Ok, I just confirmed that there are no dupes so I can't fathom why I'm still having this issue. And disregard the other question. I wasn't thinking that one of the criteria that the date table must have is that it be formatted as a date column which wouldn't be in this case.

Thanks for your help. This is so frustrating.

 

[geode]

Should I remove the formulas used to create the date table and leave them as value fields only? I actually tried that already and that didn't help but maybe I did something wrong..

 

[scottsen]

Before I forget, don't start at 1/1/1900. Only sadness leads that way. Just start with where you actually have data, or at least that same year.

I have never ever seen this "lie". If it says there are dupes, ... I have always found dupes. The most common way was via "blanks".

* Add a measure RowCount := COUNTROWS(CalendarTable).
* Create a new pivot table
* Drag CalendarTable[DateKey] onto rows
* Drag RowCount to values
* Sort by RowCount (descending)

Anything > 1?

 

[geode]

Ah, ok I will start with a lower set of dates and check for dupes again using your formula.