Power BI - Using of filters in calculate function

Question

Please refer below to the sample data i used:

ID  Name     Status         Dept.
1   Austin   Pending        MES
2   Roy      Devilered      DHA
3   Steven   Terminated     DHA
4   Peter    Pending        MES
5   Sanjay   Pending        MES
6   Domnic   Terminated     LA
7   Leon     Devilered      MES
8   Sanal    Devilered      LA
9   Kevin    Terminated     LA
10  Binoy    Pending        DHA

The Table name is Employee.

I added two measures:

Count_1 =    
    CALCULATE(COUNT('Employee'[ID]),
    'Employee'[Dept.]="LA",
    'Employee'[Status]="Terminated")

Count_2 = 
    CALCULATE(COUNT('Employee'[ID]),
    FILTER('Employee','Employee'[Dept.]="LA"),
    FILTER('Employee','Employee'[Status]="Terminated"))

Without any report layer filters, both measures return the value of 2. But when I add a report layer filter for Status in (Delivered,Pending), the count changes to

Count_1 gives 2

Count_2 gives blank

• It would be really helpful if someone could explain how the filters affects the results in detail
• Difference between filter, filter(all), filter(allexcept) ,filter(allselected)

Answer 1

The difference between your two measures is that the first one will ignore the existing filter context (which you've set in your report), while the second one will keep the existing filter context.

When you add the filter for Status in (Delivered,Pending) in your report, you will have the following records left in your filter context:

ID  Name     Status         Dept.
1   Austin   Pending        MES
2   Roy      Delivered      DHA
4   Peter    Pending        MES
5   Sanjay   Pending        MES
7   Leon     Delivered      MES
8   Sanal    Delivered      LA
10  Binoy    Pending        DHA

*I have changed the "Devilered" values to "Delivered".

Your first measure (Count_1) will ignore this filter context, and therefore returns the count of [ID]s in the following dataset:

ID  Name     Status         Dept.
6   Domnic   Terminated     LA
9   Kevin    Terminated     LA

Your second measure (Count_2) will keep the existing filter context, and will then apply an additional filter. Since no records fulfill both of these filters, the measure returns a Blank value.

Because your first measure ignores the existing filter context, you could effectively rewrite this:

Count_1 =    
CALCULATE(COUNT('Employee'[ID]),
'Employee'[Dept.]="LA",
'Employee'[Status]="Terminated")

To this:

Count_1 =    
CALCULATE(
    COUNT('Employee'[ID]),
    ALL('Employee'),
    FILTER(
        'Employee',
        'Employee'[Dept.]="LA" 
        && 'Employee'[Status]="Terminated")
)

And end up with the same result. I think this exposes more clearly how your first measure effectively behaves.