I have the following dimensions:
dim_date
dim_customer
dim_products
fact_sales_history
fact_lifetime_sales
The dim_date is a standard time dimension
The dim_customer dimension holds attributes about customers in the database
The dim_products dimension holds attributes about products
fact_sales_history is all order history for customers
fact_lifetime_sales holds the total spend for the customer regardless of time
I've modelled this and in theory it's working correctly however from an end users' perspective it isn't. The user wants to select sales for yesterday and filter on some dim_product attributes. Then they want to display a list of the customer numbers who've purchased and the value of the spend (for the selected date range). This works fine (queried in Excel as a pivot table). As soon as the life time spend is added from the fact_lifetime_sales measure group all customers are displayed (as the only relationship between fact_lifetime_sales and dim_customer is the customer_id).
They only want to see the lifetime spend of those customers who have purchased from the other filters they've selected.
This could be handled easily in T-SQL with an INNER JOIN - Is it possible to enforce the same logic somehow in SSAS?
See example of issue from Pivot table screenshots
Any suggestions would be appreciated.