Forum Discussion

WBscooby's avatar
WBscooby
Icon for Helper III rankHelper III
3 years ago
Solved

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

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






 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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