Hi Mynda

I have a dimension table that contains customer data, the primary key being the customer ID. I then use this to link to my fact table for transactions. However, I am required to to create a forecast of customer spend based on previous fortnightly spend. So I now have a table that looks like this: -

Table 1
Customer ID,       Customer name,        Fortnightly forecast spend

The  'Fortnightly forecast spend' column will be used in pivot tables 'values' section violating one of the principles of not to use dimension tables in the values column. I am also using it in a DAX formula to create my 12 month forecast for customer spend. This seems to be resembling more of a fact table than a dimension table. I thought about creating a separate dimension table that is simply: -

Table 2
Customer ID,      Customer name

I was then thinking of creating a relationship between the two tables and using table 2 as my dimension table to link to my other fact tables. Would this be acceptable? I understand that Power pivot can't handle 1-to-1 relationships. Does this make any difference?

I can imagine a similar situation where you have a table of your inventory/stock. Each product would be unique but have value fields for opening inventory, materials used in production, closing inventory.

What would be the solution in these instances? Thank you.

Tim