Thanks:  0
Likes:  0

Thread: combining two slightly different datasets

1. combining two slightly different datasets

I have two tables

TableA
 name date property2 key eventa 1-1-2012 xxx 1-1-2012xxx eventb 2-5-2012 yyy 2-5-2012yyy eventc 2-5-2012 yyy 2-5-2012yyy

of which I generate this pivot table:

 1-1-2012 xxx 1 yyy 2-5-2012 xxx yyy 2

also tableB
 name date property2 value key(unique) action1 1-1-2012 xxx 99 1-1-2012xxx action2 2-5-2012 yyy 55 2-5-2012yyy

Now I'd like to combine the two by creating a relationship between the two key columns above.

What I'd like to see is this:

 1-1-2012 xxx 1 99 yyy 2-5-2012 xxx yyy 2 55

but what I get is this:

 1-1-2012 xxx 1 99 yyy 99 2-5-2012 xxx 99 yyy 2 99

How could I relate those two values correctly?

2. Re: combining two slightly different datasets

As I see it, you used :
tableB[date]
tableA[property2]
countrows measure on tableA
A measure based on tableB[value]

Using property2 from tableB (your dimension) should solve the issue.

Explanation: for given measures, PowerPivot will show all combinations of attributes for which facts exist for at leat one measure.

In your case, this translates to:
Show all combinations of tableB[date] and tableA[property] for which there exists some tableB[value].

Of course for every value in tableB[date] there exists some tableB[value] independantly of what the value of tableA[property2] is.

When you have a dimension table, it is best to hide the redundant key columns from the fact table, to avoid this type of confusions.

3. Re: combining two slightly different datasets

However the problem is that in neither of the tables, the property2 value is unique; thats exactly the reason I created a calculated column of keys.
I should have made that clearer in the example.

As you might remember: the property2 represents the bucket field of my previous problem.
Table B represents this shift information in with wich a aggregate totals per time-bucket.

Table A represents a database of revenue of which time-bucket information is a property. This is a separate model.

I'd like to combine the two models to align the cost of personel per time-bucket with the revenue in the same time-bucket.

4. Re: combining two slightly different datasets

Say you have the following structure, with the appropriate - and hopefully obvious - relationships :
Dates {date}
Buckets {bucket}
Costs {date, bucket, cost}
Revenue {date, bucket, revenue}

Now you could simply have a pivot table with:
Dates[date] , Buckets[bucket] on rows
SUM(Costs[revenue]), SUM(Revenue[revenue]) in values.

In other words, you should add a Date table to your model, if you do not have one already. Link TableA and TableB to the Tijd and Date dimensions. Link all other tables to your Date dimension as required. Hide the keys in the data tables.

5. Re: combining two slightly different datasets

Adding a date table was indeed the solution.

With your help I think I'll be able to make the model complete.

Many thanks indeed.

6. Re: combining two slightly different datasets

On the same approach proposed by Laurent, here is a link to a blog entry I wrote some time ago discussing this exact same issue. I may help as well in cases in which neither table has the complete list of lookup values:

Merging data in PowerPivot « Javier Guillén

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•