Forum Discussion
Sub query, selection criteria
I am hoping for some advice please re: best approaches to handle a scenario that is similar to a sub-query.
Assessment table
| Date_assessment | Asessment_Period | Score | Customer_ID_FK |
| 1/01/2019 | start | 11 | AAA1 |
| 1/02/2019 | 1 month | 12 | AAA1 |
| 1/03/2019 | 2 month | 13 | AAA1 |
| 1/01/2019 | start | 15 | AAA2 |
| 1/02/2019 | 1 month | 16 | AAA2 |
| 1/03/2019 | 2 month | 17 | AAA2 |
| 1/01/2019 | start | 20 | AAA3 |
| 1/02/2019 | 1 month | 21 | AAA3 |
| 1/03/2019 | 2 month | 22 | AAA3 |
| 1/05/2019 | start | 11 | AAA1 |
| 1/06/2019 | 1 month | 12 | AAA1 |
| 1/07/2019 | 2 month | 13 | AAA1 |
| 1/04/2019 | start | 21 | AAA6 |
Activity table
| Date_start | Date_finish | Activity_name | Customer_ID_FK |
| 31/12/2018 | 28/03/2019 | Activity_1 | AAA1 |
| 15/12/2018 | 2/03/2019 | Activity_2 | AAA1 |
| 1/04/2019 | Activity_1 | AAA1 | |
| 31/12/2018 | 15/02/2019 | Activity_3 | AAA2 |
| 15/12/2018 | 2/03/2019 | Activity_5 | AAA2 |
| 1/04/2019 | Activity_3 | AAA6 | |
| 31/12/2018 | 15/02/2019 | Activity_1 | AAA3 |
| 15/12/2018 | 2/03/2019 | Activity_2 | AAA3 |
| 1/04/2019 | Activity_1 | AAA3 | |
| 1/05/2019 | Activity_1 | AAA1 | |
| 1/05/2019 | Activity_2 | AAA1 | |
| 1/05/2019 | Activity_1 | AAA1 |
Customer table
| Customer_ID_PK | Name | DOB |
| AAA1 | cust1 | 1/02/2000 |
| AAA2 | cust2 | 1/04/2000 |
| AAA6 | cust6 | 1/05/2000 |
| AAA3 | cust3 | 1/06/2000 |
1- "customer" m-assessments; 1- "customer" m-"activities"
The requirement is to create a a filtered "Assessment" result set from the "Assessment" table for Customers whose "Activity" i.e. their "Date_start" and "Date_finish" has been within a selected "Date from" and "Date to".
I have been able filter the Customers in the "Activity" table whose activity has been within a selected "Date from" and "Date to" using a variables and a disconneced date slicer/calendar, however I am not sure of the best approach to also filter the "Assessment" table for these Customers only i.e. to filter Assessments for these customers who have been active in within the selected date range only - it is necessary to consider that further dax measures would need to be developed for this filtered "Assessment" result set because this would be considered the base result set to analyze.
Any advice would be most appreciated.
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
)
)
)
)
)
2 Replies
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
)
)
)
)
)
