How do I make DAX measures respect cross-filtering from other visuals?

[SQLguy]

Hi all,

I need help to write a DAX measure that will respond to cross-filtering. I'm developing a report for a hospital on their homeless patient population, and I'm trying to show the percent of appointments that were completed, canceled, and no-showed based on an [Appt Status] column. I have three different sets of measures, one for each possible status. Everything works fine until that column is cross-filtered. The measures for "Canceled" status are below:

Selected Appointments = COUNTROWS( 'Appointments' )

Canceled Appointments = CALCULATE(
    [Selected Appointments],
    Appointments[Appt Status] = "Canceled [3]"
)

% Canceled = IF(
    [Selected Appointments] = 0,    --If there are no appts in the denominator, 
    BLANK(), --then display blank.
    DIVIDE( [Canceled Appointments], [Selected Appointments] ) + 0 --If there were some appts, but none were canceled, show 0.0%.
)

The underlying data looks something like this: 

In the table below, I summarize the % of appointments completed and canceled by department category. The bar chart shows the total number of appointments by status. When I click on the bar chart and cross-filter by "Canceled" status, the "Appts" column in the table is filtered, but "Completed Appts" and "Canceled Appts" (which come from my DAX measures) do not change. As a result, "% Completed" has a non-sensical result. How do I rewrite my measures so that they will respect the cross-filtering and evaluate to zero if a particular appointment status is not in the cross-filtered filter context?

Thanks in advance for your help!

[OwenAuger]

Hi @SQLguy 

I recommend wrapping the Filter argument of CALCULATE in KEEPFILTERS.

Normally, the Filter arguments of CALCULATE overwrite existing filters, but using KEEPFILTERS modifies this behaviour so that they are intersected with existing filters.

For example, your Canceled Appointments measure would be:

Canceled Appointments =
CALCULATE(
    [Selected Appointments],
    KEEPFILTERS ( Appointments[Appt Status] = "Canceled [3]" )
)

Regards,

Owen

[SQLguy]

Thank you very much, @OwenAuger  and @jdbuchanan71! KEEPFILTERS solved the problem! 

[jdbuchanan71 · Accepted solution]

Try adding a KEEPFILTERS to the CALCULATE:

Canceled Appointments = CALCULATE(
    [Selected Appointments],
    KEEPFILTERS(Appointments[Appt Status] = "Canceled [3]")
)

This will respect other filters that are also flowing into the measure.