Forum Discussion
Creating a filtered table based on another one
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.
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] ) )
4 Replies
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] ) )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.