Filter table using date slicer, then filter dates in another table based on results

[WBscooby]

Hi

I'm going around in circles again! I've mocked up a very quick PBI file due to the sensitive nature of my own data. Essentially I have a date calendar, Customer Table, intervention table and attendance table.

The customer can have many interventions and the customer can have many different rows in attendance. The tables are not really related.

Using a date slicer, I need to select all records in intervention that have an end date in the selected range. For each intervention ID, I then need to use the end date to see if the customer has any attendance on this date. There may be more than one attendance record that applies. I simply want to show the attendance records where applicable in a table.
I'm not sure if it would be best to work in power query and create a bridge table linking the two tables although they don't really have a relationship or if I need to figure out the dax coding... in which case, I am struggling!

Any help would be appreciated. Thank you 🙂 

IntAttendance_Test.pbix






 

[WBscooby]

This is great, thank you!

[v-cgao-msft · Accepted solution]

Hi @WBscooby ,

Please create a new measure and use it as visual's filter.

Measure = 
CALCULATE (
    COUNTROWS ( 'SchoolAttendance' ),
    TREATAS (
        DISTINCT ( 'Intervention'[Customer ID] ),
        'SchoolAttendance'[Customer ID]
    ),
    FILTER (
        'SchoolAttendance',
        'SchoolAttendance'[Attendance Start Date]
            <= CALCULATE (
                MAX ( 'Intervention'[Int End Date] ),
                RELATEDTABLE ( 'Intervention' )
            )
            && 'SchoolAttendance'[Attendance End Date]
                >= CALCULATE (
                    MAX ( 'Intervention'[Int End Date] ),
                    RELATEDTABLE ( 'Intervention' )
                )
    )
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum