# sort of lookup/measure question!!!



## masplin (Apr 24, 2012)

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


----------



## ruve1k (Apr 24, 2012)

Why don't you put Visitor Month as row labels instead of Transaction Month?


----------



## masplin (Apr 25, 2012)

Well switching the transaction month to visit month for row labels seems to work, but I have no idea why? can you explain???

My next problem is one of the columns I'm after is registered users/new vistors to get website conversion. I have a 3rd table "users" where a count of User ID for a given "Reg Month" gives me user registering the month.  Now I can't create a relationship between Reg Month and Visit Month as visits already related to Trans Month.   If I want to add a column for registered users usually it is just click on User ID and change to count, but agian this gives me the total ever not the month as doesn't understand the row label.

Trying a measure like this just throws an error as says the filter can only have one column.


```
=CALCULATE(count(users[User ID]),users[Reg Month]=WebStats[Visit Month])
```
I basically have a date which is sort of "ACTIVITY MONTH" , but it appears in 3 tables with 3 differnet names. Is there anyway of bringing this data together on one pivot table?

Thanks Mike


----------



## ruve1k (Apr 25, 2012)

Please post samples of each table and clearly explain the relationship(s) between them so that we can have a meaningful conversation instead of me shooting in the dark. Thanks.


----------



## masplin (Apr 25, 2012)

Hope this makes more sense
User table

-- removed inline image ---


Using the "visit month" column in the pivot table even though titled transactions. New loads is calc by counting in transactions table, New vistors now coming form webstats correctly.  Registrations is count of user ID in user table for the month in question and then I can divide by new visitors to get %conversion. Has to come from the User table as they won't appear in the tranasction table until they do something. 

Thanks again


----------



## masplin (Apr 25, 2012)

sorry the tables didnt paste in. Had a hunt around and there was a tool HTML maker for doing this but currently unavailable. Is there any other way for me to post the table structure please?


----------



## ruve1k (Apr 25, 2012)

The HTML Maker was available when I tried. Personally, I use the JMT Excel Utilities add-in which has a tool to export (copy) a range to HTML.


----------



## masplin (Apr 25, 2012)

I'm sorry I'm being extremely stupid. I got the utility installed and selected the cells in the pivot table and copy to html. I'm too stupid to be able to work out how to get the html off the clipboard and into the post!

Even so that doesn't help me to show you the structure of the 3 underlying powerpivot tables as can only select the whole columns. If I can get the above to work I can mock up in excel if that is the only way to do it.

Thanks Mike


----------



## ruve1k (Apr 25, 2012)

Just paste it in and then click the Preview Post button.


----------



## masplin (Apr 25, 2012)

ok got it for excel. Is there anyway to show the powerpivot table layout as trying to explain the relationships?


----------



## masplin (Apr 24, 2012)

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 (Apr 25, 2012)

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 (Apr 25, 2012)

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 (Apr 25, 2012)

masplin said:


> 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 (Apr 25, 2012)

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 (Apr 25, 2012)

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 (Apr 25, 2012)

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 (Apr 26, 2012)

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

<table><thead><tr><th>Active</th><th>Table</th><th>Related Lookup Table</th></tr></thead><tbody><tr><td>Yes</td><td>transactions [User ID]</td><td>users [User ID]</td></tr><tr><td>Yes</td><td>transactions [Gym ID]</td><td>gyms [Gym ID]</td></tr><tr><td>Yes</td><td>transactions [Product type]</td><td>TheorComm [Product Type]</td></tr><tr><td>Yes</td><td>transactions [Transaction Month]</td><td>WebStats [Visit Month]</td></tr><tr><td>Yes</td><td>users [Registration voucher code]</td><td>voucher codes [voucher Code]</td></tr><tr><td>Yes</td><td>users [City Code]</td><td>PostCodes [City Code]</td></tr><tr><td>No</td><td>users [Reg Month]</td><td>WebStats [Visit Month]</td></tr><tr><td>Yes</td><td>gyms [Gym City Code]</td><td>GymPostCode [City Code]</td></tr><tr><td>Yes</td><td>products [Gym ID]</td><td>gyms [Gym ID]</td></tr></tbody></table> 
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 (Apr 26, 2012)

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 (Apr 26, 2012)

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 (Apr 26, 2012)

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


----------



## masplin (Apr 24, 2012)

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 (Apr 26, 2012)

Is this because I have created an indirect multiple relationship between tables?  I found this on microsoft Technet

"Multiple relationships could result in ambiguous dependencies between  tables. To create accurate calculations, you need a single path from one  table to the next. Therefore, there can be only one relationship  between each pair of tables."

Transcation is related to user both directly through User ID and indirectly through both relating to Webstats. Is there any work around?


----------



## masplin (Apr 27, 2012)

If anyone wonders I solved this by calculating the registration in extra an column in the webstats page using the obvious formula that wouldn't calc as a measure due to the context.


```
=CALCULATE(counta(users[User ID]),filter(users,users[Reg Month]=WebStats[Visit Month]))
```


----------



## Ben Niebuhr (May 14, 2012)

Hi Mike,

This looks like an older thread, but I thought you might like an alternative:

Have you tried to create a separate date table with a column named Activity Month? You should then be able to link all others to this new table, and use it as your row/column/slicer data. Plus you can add other columns for quarter, year, Moon phase, year of the rat, etc.

Whenever I create a PowerPivot book, the second thing I do is create a date table. The third thing is to marvel at the flexibility I have to do time based analysis.

Let me know if I am missing the point of your issue,

Ben Niebuhr


----------

