Creating a filtered table based on another one

[Pedro503]

Here's the link to the file

Hey there

I'd like to create a table that filters every row of my Fact table as long as the rows' dates are prior or equal to the due date (stored on its own table named 'f_due_user')

Here's the code I've authored but didn't get the expected outcome:

Summarized Table = 
CALCULATETABLE (
    SUMMARIZE ( 
        'Fact', 
        'Fact'[User_id], 
        'Fact'[Subscription_date] 
        ),
    'Fact'[Subscription_date] <= MIN ( f_due_user[Due_payment] ),
    TREATAS( VALUES( 'Fact'[User_id] ), f_due_user[User_id] ),
    TREATAS( VALUES( 'Fact'[Subscription_date] ), f_due_user[Due_payment] )
)

I've even tried it by simply using FILTER() as follows, but it did not works either:

VAR _DueDate = MIN( f_due_user[Due_payment] )
VAR _CurrentUser = SELECTEDVALUE( 'Fact'[User_id] )
VAR _Result =
    FILTER(
        ALL( 'Fact' ),
        'Fact'[Subscription_date] <= _DueDate -- && 'Fact'[User_id] = _CurrentUser
    )
RETURN
    _Result

Taking as an example the user_id = 22 (but not limited solely to this user_id), here's what I'd like to do:

Any thoughts on how this table could be created?

Thanks in advance.

[ThxAlot · Accepted solution]

In your data model, I didn't see any necessity of d_users since f_due_user[User_id] is a distinct column; you can built a relationship between f_due_user (1:*) Fact by [User_id].

Summarized Table.pbix

Anyway, the solution based on your current model,

Summarized Table = 
GENERATE(
    f_due_user,
    VAR __due = f_due_user[Due_payment]
    RETURN
        SELECTCOLUMNS(
            CALCULATETABLE(
                SUMMARIZE( 'Fact', 'Fact'[User_id], 'Fact'[Subscription_date] ),
                CALCULATETABLE( d_users ),
                'Fact'[Subscription_date] <= __due
            ),
            "@subscription", 'Fact'[Subscription_date]
        )
)

[Pedro503]

It's exactly what I was looking for, thanks a lot.

Could you explain me whats the point of this CALCULATETABLE() inside another CALCULATETABLE? Dind't get what it is performing on this formula. 

Once again, appreciate your help 👍

[ThxAlot]

Glad that my proposal helps!

In the current data model, filters on f_due_user can't be propagated to Fact table in a natural manner; thus Expanded Table ( CALCULATETABLE(d_users) ) can be leveraged here for filter propagation. 

[Pedro503]

You're totally right. Thanks for your explanation.