How to filter a set of rows based on two overlapping filters? (more complicated than title suggests)

[D_PBI]

Hi
Please view the below screenshot of the data model to work with.

There are two tables Parent and Child. The join between them is based on the RecordID field. It is (should be) a 1-* cardinality.
The report will contain a table visual that includes fields from both the Parent and Child tables. A reason why I haven't merged the Child table into the Parent table is due to another page, in the same report, showing Child fields in a standalone manner, and not every Child record has a Parent record (bad data - see RecordID 7 for an example).

If I were to drag the fields from both tables, with the Parent table being the driver for this visual, then the table would look like the below:

I would like your help on the following.
The user should have a slicer to show either all records (so the all the records shown in the immediate above table). Or to show the relevant records which are those records that have an entry in both the Parent and Child tables (i.e. RecordID field present), and have a date in the specified timeframe. This specified timeframe will be set by a relative date slicer on the Date field.
Note: when I say all or relevant records, these records may still be filtered by any other slicers on the page. We just need to factor that into any DAX.

I'm thinking I need to 'enter data' to create a table to use as the slicer's contents, something like the below:

I will also need a date filter to allow the user to select the relative date, like the below:

Taking the dummy table (shown again, below). If the user chose 'All records' then all the rows below will be returned (barring no other slicer has been configured - i.e. a user selects Type = Temp, this would reduce the rows shown just to the last two).

If the user was to select 'Relevant records' and the relative date slicer was set to the next 12 months (always starting from day the report is used (i.e. TODAY() ) which is currently the 22/09/23) then I'd want to show the RecordIDs that fulfil those conditions. The expected rows shown would be:

Notice how at least one Child record, per RecordID, has a Date that falls in the next 12 months (from today being the 22/09/23). As this condition is met, I'd want all records with those RecordIDs to be shown regardless if the other Child records, for that RecordID, fall in the next 12 months or not. Again, these returned rows are subject to any other slicers being configured.

I hope this all makes sense. If you can help me achieve this, it will be much appreacited. Thanks.

[v-stephen-msft · Accepted solution]

Hi @D_PBI ,

Here's the result. Hope that helps.

You can check my attachment for more details.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[D_PBI]

@v-stephen-msft - It works as needed. I really appreciate your help with this. Thanks very much.

[D_PBI]

Bump. Is anybody able to advise? Thanks.