Forum Discussion
Creating a filtered table based on another one
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].
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] ) )
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].
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]
)
)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 👍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.
You're totally right. Thanks for your explanation.