Modeling challenge with filter propogation...

[billtaney]

Hi there. I wonder if anyone can help...I'm going a little mad!

See screen shot of the model containing the problem I've described below - https://ibb.co/i0efL6

I have an existing model holding two fact tables using multiple lookup dimensions (these lookups are out of sight below sales/returns measure groups), following a star schema quice nicely. 

Recently, I've introduced the concept of a "User" dimension which connect to the Sales/Returns fact tables on id_user.  You can see this above the Sales/Returns measure groups.

Additionally, I've introduced a new historical fact table called "User Activity Status Measures" which tracks users through different Activity Statuses during their lifetime. E.g. New / Existing etc.  The logic for this is handled in the ETL and presented in the table with start and end dates. This joins on User dimension using id_user.

I have a number of measures within this fact, one example being "New Customers".  To give you an example, this is used in combination with the "User Activity Date" dimension in DAX as below to pinpoint users with this status over time:

New Customers :=
CALCULATE (
    DISTINCTCOUNT ( 'User Activity Status Measures'[id_user] ),
    FILTER (
        'User Activity Status',
        'User Activity Status'[User Activity Status] = "New"
    ),
    FILTER (
        'User Activity Status Measures',
        'User Activity Status Measures'[dt_user_activity_status_history_end]
            >= CALCULATE ( MAX ( 'User Activity Date'[Date] ) )
    ),
    FILTER (
        'User Activity Status Measures',
        'User Activity Status Measures'[dt_user_activity_status_history_start]
            <= CALCULATE ( MAX ( 'User Activity Date'[Date] ) )
    )
) 

This measure is working nicely, and I am filtering sales/returns by "New Customers" (either as of today or a point in time) using Activity Status Date dimension in combination with "Order Date", dimension which is also connected to Sales / Returns on id_date. I'm also able to slice on sales/returns metrics values per new customers, no problems here...

 BUT... a problem is introduced with the Sales/Returns metrics when I try and bring the "User Activity Status" lookup dimension across the rows.

It appears that the Sales/Returns aren't able to be filtered by the split of the User Activity Status, although I can get the filter of Users to work in the measures held in "User Activity Status Measures" table directly like with New Customers.

Is this a problem with filter propogation? E.g. the Sales/Returns can't propogate back to the "User Activity Status Measures" table? I've tried to enable bi-directionl filtering from Sales Measures --> User, however this doesn't work because I've already got a star schema with my original Sales/Returns facts connected to multiple lookups and I get filter path errors.

I've also thought about inactive relationships, but to be honest I'm going round in circles trying to work out the best way to apply this. I've tried all sorts 😞

Essentially, I need to copy this pattern for a number of other User related functionality (E.g. Tracking Customer Category over time, other segmentations) using the same approach as User Activity Status through adding new Measure tables with start/end dates, but it's very important I can filter/slice the sales/returns metrics by the lookup values. I'm concerned this approach isn't going to work.

Any help from you talented bunch would be most appreciated.

Bill.

[v-yuezhe-msft]

@billtaney，

If you create the data model in Power BI Desktop, please help to share the PBIX file for us to analyze. 

However, if you define the data model in Excel and create pivottables, please post the question in SSAS forum or PowerPivot forum to  get dedicated support.

Regards,
Lydia

[billtaney]

Is there anyone who can help or at least point me in the right direction? Thank you!