Forum Discussion
Sub query, selection criteria
Relationships
Outputs
Ok, thank you for your advice. I have found a way forward that may be suitable (for now).
Relationships have been created as above in the relationships image.
To create the complex selection parameters to filter only those customers who have been active within a selected dates in the activity table :
1. A ‘date from’ and a ‘date to’ have been captured as variables via a disconnected date slicer and used in measures:
DATE_FROM = VAR date_from = FIRSTDATE(ALLSELECTED(CAL_DATE[DATE])) return date_from
DATE_TO = VAR date_to = LASTDATE(ALLSELECTED(CAL_DATE[DATE])) return date_to
2. Next another measure was created to only flag / count active customers for the selected date range, this has also been used as a visual filter in the outputs image above and also helps identify assessments for only the active customers in the selected date range:
ACTIVE_CLIENT_FLAG =
COUNTAX (
'Activity table',
IF (
ISBLANK ( 'Activity table'[Date_finish] ) = TRUE ()
&& ( 'Activity table'[Date_start] >= [DATE_FROM]
&& 'Activity table'[Date_start] <= [DATE_TO] ),
1,
//a: above this flags new customers currently enrolled in the selected period
IF (
ISBLANK ( 'Activity table'[Date_finish] ) = TRUE ()
&& ( 'Activity table'[Date_start] < [DATE_FROM] ),
1,
//b: above this flags continuing customers not yet completed in the selected period
IF (
( 'Activity table'[Date_finish] >= [DATE_FROM]
&& 'Activity table'[Date_finish] <= [DATE_TO] )
&& ( 'Activity table'[Date_start] >= [DATE_FROM]
&& 'Activity table'[Date_start] <= [DATE_TO] ),
1,
//c: above this flags new customers for the selected period who completed in selected period
IF (
( 'Activity table'[Date_finish] >= [DATE_FROM]
&& 'Activity table'[Date_finish] <= [DATE_TO] )
&& ( 'Activity table'[Date_start] < [DATE_FROM] ),
1
)
)
)
)
)
Hi mleepin
If you’d like to make the filter applied from Activity table to Assessment table, it’s not accessible based on the relationship auto-detected. The limitation- regarding to chains of relationship- is that all the relationship need to be of the same type, and all of them going in the same direction.
You can manage the relationship like this below:
When you using the calendar as slicer to filter Activity table, the customer table would be filtered as well, and the condition will pass to Assessment table finally.
Please keep me updated whether it works.
Best regards,
Dina Ye
Relationships
Outputs
Ok, thank you for your advice. I have found a way forward that may be suitable (for now).
Relationships have been created as above in the relationships image.
To create the complex selection parameters to filter only those customers who have been active within a selected dates in the activity table :
1. A ‘date from’ and a ‘date to’ have been captured as variables via a disconnected date slicer and used in measures:
DATE_FROM = VAR date_from = FIRSTDATE(ALLSELECTED(CAL_DATE[DATE])) return date_from
DATE_TO = VAR date_to = LASTDATE(ALLSELECTED(CAL_DATE[DATE])) return date_to
2. Next another measure was created to only flag / count active customers for the selected date range, this has also been used as a visual filter in the outputs image above and also helps identify assessments for only the active customers in the selected date range:
ACTIVE_CLIENT_FLAG =
COUNTAX (
'Activity table',
IF (
ISBLANK ( 'Activity table'[Date_finish] ) = TRUE ()
&& ( 'Activity table'[Date_start] >= [DATE_FROM]
&& 'Activity table'[Date_start] <= [DATE_TO] ),
1,
//a: above this flags new customers currently enrolled in the selected period
IF (
ISBLANK ( 'Activity table'[Date_finish] ) = TRUE ()
&& ( 'Activity table'[Date_start] < [DATE_FROM] ),
1,
//b: above this flags continuing customers not yet completed in the selected period
IF (
( 'Activity table'[Date_finish] >= [DATE_FROM]
&& 'Activity table'[Date_finish] <= [DATE_TO] )
&& ( 'Activity table'[Date_start] >= [DATE_FROM]
&& 'Activity table'[Date_start] <= [DATE_TO] ),
1,
//c: above this flags new customers for the selected period who completed in selected period
IF (
( 'Activity table'[Date_finish] >= [DATE_FROM]
&& 'Activity table'[Date_finish] <= [DATE_TO] )
&& ( 'Activity table'[Date_start] < [DATE_FROM] ),
1
)
)
)
)
)