Sub query, selection criteria

[mleepin]

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.

[v-diye-msft]

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

[mleepin · Accepted solution]

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

                )

            )

        )

    )

)