Filtering across tables

[naa217]

Hi, 

I have created a table in PowerBI to summarise how many appointments each patient has attended, and how many appointments each patient has DNA'd. The table counts rows from my master appointments table based on certain conditions using the following coding: 

InterventionSummaryTable=

VAR SummaryTable =

    SUMMARIZE(

        FILTER(

            ALLSELECTED('Report Source Appointments'),

            NOT(ISBLANK('Report Source Appointments'[Unique ID]))

        ),

        'Report Source Appointments'[Unique ID],

        "Number of Intervention Sessions Attended",

            CALCULATE(

                COUNTROWS('Report Source Appointments'),

                'Report Source Appointments'[Session Type] = "Intervention" &&

                'Report Source Appointments'[Attendance] IN {"Attended on time", "Attended late but seen"} &&

                NOT(ISBLANK('Report Source Appointments'[End Date]))

            ),

        "Number of Intervention Sessions DNA",

            CALCULATE(

                COUNTROWS('Report Source Appointments'),

                'Report Source Appointments'[Session Type] = "Intervention" &&

                'Report Source Appointments'[Attendance] = "DNA" &&

                NOT(ISBLANK('Report Source Appointments'[End Date]))

            )

    )

RETURN

    SummaryTable

When I apply an appointment date filter to my master appointments table, the InterventionSummaryTable filters for the Unique ID's that are in the master appointments table, however the "Number of Intervention Sessions Attended" and "Number of Intervention Sessions DNA" columns do not apply the filter and instead I just get a total count for those Unique IDs.

I have a relationship established between my Report Source Appointments and InterventionSummaryTable based on Unique ID. 

Does anybody know how I can ensure that the "Number of Intervention Sessions Attended" and "Number of Intervention Sessions DNA" columns in my InterventionSummaryTable only count filtered data? 

Thanks! 

[TomMartens · Accepted solution]

Hey @naa217 ,

this is not possible. Your DAX statement returns a table, a so-called "virtual table". A virtual table will only be "calculated" when the semantic model is refreshed, the same is valid for calculated columns. This is completely different if the DAX returns a scalar value, meaning a measure. A measure will be re-evalutated whenever a user interacts with the model, e.g., changing the selection of a slicer.

Hopefully, this helps to tackle your challenge.

Regards,

Tom