Problem with Time intelligence in PowerPivot.

[mrxlsx]

Good afternoon everybody here,

For last 3 or 4 weeks, I have been looking intensely for a solution to my problem over internet. But badluck kicked my ***. I couldn't find any article addressing this issue.

I have few Excel tables...namely fSa*****ok, dStoreIDs, dCalendar, dFreightKeys, dProducts, dCustomers, dEmoluments etc.

Recently, I added a table dTime trying to do some intelligent calculations like in what time my sales are at peak in a particular hour of the day. But I am unable to establish the relationship with the Time column in my fSa*****ok to dTime table as PowerPivot says "Relationship cannot be created because each column contains duplicate values, Select at least one column that contains only unique values".

In the dTime table I have unique times i.e. from 00:00:00 till 23:59:59 (86400 unique time values). But why couldn't I link this with the Time column in my fSa*****ok when I have unique values in my dTime table.

I think I clearly explained the problem, But if you need better picture, I can give only the dTime table at the max. I have no right to share the real data in any way. Ask more questions if you cannot understand anything here. Thanks a ton for all.


mrxlsx.

 

[mrxlsx]

Scottsen,

I am sending a piece from the actual file. Please try to establish relationship as you did before on the previous file. I still couldn't do this for some reason.

I think it is not working on this particular file only. If you can do this, I assume some thing wrong with the formats at my end.

BTW, I am working on Excel 2010's PowerPivot.

https://drive.google.com/open?id=0B6GD5M1_zN7bOTFDRFEyODVwREU&authuser=0

mrxlsx

 

[mrxlsx]

Hi,

At last I achieved it man..But thanks for your effort, Scottsen.

mrxlsx

 

[scottsen]

Woooo! My mere presence scared it into working

 

[mrxlsx]

Somehow it was solved, that's enough for me. One more angle to the question.

If I had all my Dates like this format mmm/dd/yyyy hh:mm:ss in fSa*****ok's Date column, can I link the same column to dCalendar table'sDate as well as dTimetable's Time column. Because it is having both dates and times in the column, right. Will it work or I have to write some DAX for that to work?

mrxlsx.

 

[scottsen]

You will need columns of the same type to make a relationship. If you need to create a relationship on date AND time, you will need a single column with date AND time.

 

[mrxlsx]

Ok..I got the gyan now. thanx.