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]

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]

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]

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