sort of lookup/measure question!!!

[masplin]

I have a table "transactions" which includes a field "transaction month" formatted so its 1st of each month. I have another table "Webstats" with website vistor numbers in a column "web visitors" organised by "Visitor month", also formatted 1st of each month. I have created a realtionship between "transaction month" and "visitor month". I can ceate a column in transactions using retalte that gett he right web vistors for the mnoth of each transaction so I know the RELATE is working.

I have a pivot table with "transaction month" as the row label and assorted columns. I want one of the columns to be the vistors in that month taken from the web stats table. I have tried everything I can think of with no success.

If I use a measure RELATED(web Stats[web vistors]) I get the total visitors for all months repeated in each line not just the vistors for that transaction month. I was expecting the transaction month filter to just pick the relevant month. I have tried using various filters to make transaction month=vistor month, but I just get errors.

I'm sure this is trivial for you smarter folk out there so any hep appreciated.

Mike

 

[masplin]

Ok so here are the 3 powerpivot tables. Transaction month and Vistor month are related. user ID is related between transactions table and users table. Apolgise for rubbish formatting.






















<table border="1" bordercolor="#999999" cellspacing="0"> <tbody><tr><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF">Transaction </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="84" bgcolor="#FFFFFF">User ID </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="127.5" bgcolor="#FFFFFF">Transaction </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="133.5" bgcolor="#FFFFFF">transaction month </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="center" width="84" bgcolor="#FFFFFF">133 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="127.5" bgcolor="#FFFFFF">Add </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="133.5" bgcolor="#FFFFFF">Jan-11 </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="center" width="84" bgcolor="#FFFFFF">143 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="127.5" bgcolor="#FFFFFF">Add </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="133.5" bgcolor="#FFFFFF">Jan-11 </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="center" width="84" bgcolor="#FFFFFF">133 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="127.5" bgcolor="#FFFFFF">Cashout Fee </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="133.5" bgcolor="#FFFFFF">Jan-11 </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="center" width="84" bgcolor="#FFFFFF">133 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="127.5" bgcolor="#FFFFFF">Cashout </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="133.5" bgcolor="#FFFFFF">Jan-11 </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="center" width="84" bgcolor="#FFFFFF">143 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="127.5" bgcolor="#FFFFFF">Cashout Fee </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="133.5" bgcolor="#FFFFFF">Jan-11 </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="center" width="84" bgcolor="#FFFFFF">143 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="127.5" bgcolor="#FFFFFF">Cashout </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="133.5" bgcolor="#FFFFFF">Jan-11 </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="center" width="84" bgcolor="#FFFFFF">153 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="127.5" bgcolor="#FFFFFF">Add </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="133.5" bgcolor="#FFFFFF">Jan-11 </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="27" valign="center" width="84" bgcolor="#FFFFFF">153 </td><td rowspan="1" colspan="1" align="left" height="27" valign="center" width="127.5" bgcolor="#FFFFFF">Cashout Fee </td><td rowspan="1" colspan="1" align="left" height="27" valign="center" width="133.5" bgcolor="#FFFFFF">Jan-11 </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="84" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="127.5" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> <tr><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF">Users </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="84" bgcolor="#FFFFFF">User ID </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="127.5" bgcolor="#FFFFFF">Reg Month </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="center" width="84" bgcolor="#FFFFFF">133 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="127.5" bgcolor="#FFFFFF">Jan-11 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="center" width="84" bgcolor="#FFFFFF">143 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="127.5" bgcolor="#FFFFFF">Jan-11 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="center" width="84" bgcolor="#FFFFFF">153 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="127.5" bgcolor="#FFFFFF">Jan-11 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="27" valign="center" width="84" bgcolor="#FFFFFF">163 </td><td rowspan="1" colspan="1" align="left" height="27" valign="bottom" width="127.5" bgcolor="#FFFFFF">Jan-11 </td><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="84" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="127.5" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> <tr><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF">WebStats </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="84" bgcolor="#FFFFFF">Visit Month </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="127.5" bgcolor="#FFFFFF">New Visitors </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="84" bgcolor="#FFFFFF">Jan-11 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="127.5" bgcolor="#FFFFFF"> 10,000 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="84" bgcolor="#FFFFFF">Feb-11 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="127.5" bgcolor="#FFFFFF"> 12,000 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="left" height="27" valign="bottom" width="84" bgcolor="#FFFFFF">Mar-11 </td><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="127.5" bgcolor="#FFFFFF"> 14,000 </td><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> </tbody></table>

 

[masplin]

and this is the pivot table I am aiming for. I am using "visit month" here as per your suggestion as works to pick up the visitor numbers form the webstats table. New loads is a measure counting transactions in the transaction table. Registrations woudl be a count of User ID in Users table where Reg Month = Visitor month.


<table width="461" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:4010;width:71pt" width="94"> <col style="mso-width-source:userset;mso-width-alt:2944;width:52pt" width="69"> <col style="width:42pt" width="56"> <col style="mso-width-source:userset;mso-width-alt:2645;width:47pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:3584;width:63pt" width="84"> <col style="mso-width-source:userset;mso-width-alt:4096;width:72pt" width="96"> </colgroup><tbody><tr style="mso-height-source:userset;height:24.75pt" height="33"> <td class="xl157" style="height:24.75pt;width:71pt; font-size:10.0pt;color:white;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:"Arial Narrow";border-top:none; border-right:none;border-bottom:.5pt solid #DCE6F1;border-left:none; background:#366092;mso-pattern:#366092 none" height="33" width="94">Transaction Month</td> <td class="xl158" style="width:52pt;font-size:10.0pt;color:white; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:none;border-right:none;border-bottom: .5pt solid #B8CCE4;border-left:none;background:#366092;mso-pattern:#366092 none" width="69">New Loads</td> <td class="xl158" style="width:42pt;font-size:10.0pt;color:white; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:none;border-right:none;border-bottom: .5pt solid #B8CCE4;border-left:none;background:#366092;mso-pattern:#366092 none" width="56">New Visitors</td> <td class="xl158" style="width:47pt;font-size:10.0pt;color:white; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:none;border-right:none;border-bottom: .5pt solid #B8CCE4;border-left:none;background:#366092;mso-pattern:#366092 none" width="62">New Load Conv%</td> <td class="xl157" style="width:63pt" width="84">Registrations</td> <td class="xl157" style="width:72pt" width="96">Conversion %</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl159" style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right: none;border-bottom:.5pt solid #DCE6F1;border-left:none" height="17">January 2011</td> <td class="xl160" style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092"> 24 </td> <td class="xl162" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">16,298</td> <td class="xl163" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092" align="right">0.1 %</td> <td class="xl157" align="right">500</td> <td class="xl164" align="right">3%</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl159" style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right: none;border-bottom:.5pt solid #DCE6F1;border-left:none" height="17">February 2011</td> <td class="xl160" style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092"> 144 </td> <td class="xl162" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">21,986</td> <td class="xl163" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092" align="right">0.7 %</td> <td class="xl157">
</td> <td class="xl157">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl159" style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right: none;border-bottom:.5pt solid #DCE6F1;border-left:none" height="17">March 2011</td> <td class="xl160" style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092"> 317 </td> <td class="xl162" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">31,808</td> <td class="xl163" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092" align="right">1.0 %</td> <td class="xl157">
</td> <td class="xl157">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl159" style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right: none;border-bottom:.5pt solid #DCE6F1;border-left:none" height="17">April 2011</td> <td class="xl160" style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092"> 318 </td> <td class="xl162" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">16,908</td> <td class="xl163" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092" align="right">1.9 %</td> <td class="xl157">
</td> <td class="xl157">
</td> </tr> </tbody></table>
Hope I'm making more sense. Thnaks

 

[ruve1k]

Now I can't create a relationship between Reg Month and Visit Month as visits already related to Trans Month.

I think that was only in v1. In the current version you can have two relationships mapping to Visit Month. So you'll have a many-to-one relationship from Transaction[Trans Month] to Webstats[Visit Month] and another many-to-one relationship from Users[Reg Month] to Webstats[Visit Month].
Essentially, Webstats will act as a common Dim table for the two fact tables: Transaction & Users. Then Registrations would be a simple count and would reflect the month context.

 

[masplin]

Ah Ok so have related "visitor month" to "Reg month". However if I just add a count of User ID from the User table I get the same number in every row which is the total of all User ID i.e. it is ignoring the month filter in rows.

 

[masplin]

Actually you can't have many to one table relationships. the relationship failed with this error

"You cannot activate the relationship because a set of active relationships already exists between tables transactions and WebStats."

 

[ruve1k]

I am able to create the relationships that I described.

https://docs.google.com/open?id=0BxfS46QjH42TTFRJOUdVR3ppOHc

What version of PowerPivot are you running?

 

[masplin]

I'm using 2012 downloaded about a month ago. If I try to create the Users[reg month] - webstats[visit month] relationship i get a dotted line in the diagram. If i go to manage relationships it looks like this

Active	Table	Related Lookup Table
Yes	transactions [User ID]	users [User ID]
Yes	transactions [Gym ID]	gyms [Gym ID]
Yes	transactions [Product type]	TheorComm [Product Type]
Yes	transactions [Transaction Month]	WebStats [Visit Month]
Yes	users [Registration voucher code]	voucher codes [voucher Code]
Yes	users [City Code]	PostCodes [City Code]
No	users [Reg Month]	WebStats [Visit Month]
Yes	gyms [Gym City Code]	GymPostCode [City Code]
Yes	products [Gym ID]	gyms [Gym ID]
So shows a "No". If I double click and check "activate" it give me that error. Coudl it be because there are other relationships. i'll try creating a new workbook with those 3 simple table and see if I can get what you get.

 

[masplin]

So on a simple workbook with just the tables I pasted in it works fine. so something about my real tables that it doesn't like.

 

[masplin]

If I delete the transaction=webstat relationship i can create the user-webstats. Oddly I then try to recreate the transaction-webstats it fails but again saying

"You cannot activate the relationship because a set of active relationships already exists between tables transactions and WebStats."

However there are no other relationships between these 2 tables if this one is inactive!!!!

 

[masplin]

Well I have found the problem. I have a relationship between transaction[user id] and users[user id]. if i delete this I can then create the 2 monthly relationships. However that knackers an even bigger portion of my workbook.

I deleted the user ID relationship, create the month relationship and tried to recreate the User ID relationship, but that fails with exactly the same error message as before about relationship exisitng between transaction and webstats tables.

I have no clue why what seems ot be an independent relationship is preventing the month relationships being set up so any advice appreciated.

Mike