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.

 

[scottsen]

I'm loving the censoring

Anyway, you are lying to me. You do not have unique values. Find the dupes. Probably the easiest way is to drop them on a pivot table, then add a measure. =COUNTROWS(dTime) and drop it on the Pivottable, and find the > 1.

My bet? multiple blank().

 

[mrxlsx]

Scottsen, thank you for your effort,

As per my understanding, a day will be having 86,400 seconds and that is what I have in my dTime table as each cell ticks-up with a second starting from 00:00:00. Likewise, I have 86,400 values, I did not understand where is the question of duplication. But my fSa*****ok will be having many time duplicates as it is a fact table.

Anyway I followed what you mentioned above. I got exactly 86,400 values when I see the values as count. I can see all are =1, nothing >1.

Is that what you are suggesting me to do or something else?

mrxlsx.

 

[scottsen]

That is super weird. Are you able to share your workbook with me? (dropbox, or whatever?) I have never seen power pivot "lie" about having unique vs duplicate values when creating a relationship.

 

[mrxlsx]

Scottsen,

Here is the link.. I have mimicked my actual data. If you need more ask me.

PowerBIDemo - Download - 4shared - farida perveen


thank you so much.

 

[scottsen]

I made an effort to figure out 4shared and gave up. Can you plz use OneDrive/GoogleDrive/DropBox/etc that is not an Ad-ridden fiasco?

 

[mrxlsx]

Hi Scottsen,

You can find the same file below.

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

 

[scottsen]

Perfect thanks.

I took your two tables, imported them into powerpivot, and connected the two time fields without issue. Are you doing something... different?!

 

[mrxlsx]

Scottsen,

I was eagerly waiting for your reply sitting with my desktop on a festival day here in India. I have no clue on why it is not allowing me to do? Even I have tried to do the same thing taking fresh Excel sheets.

Anyways, I will try to do it again and again and if I could not I will send you the entire dummy file for your perusal and I will explain what I want to do exactly and failing where. I will not be available for internet for the next 5 hrs from now so I will reply only after 5 hrs at least, so do not wait for me. Thank you so much for your effort.
mrxlsx.

 

[scottsen]

Happy Diwali, mrxlsx

On the dummy file you sent -- you can still reproduce the problem? Excel 2010? 2013? What version of PowerPivot?