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
filter
, filter(all)
, filter(allexcept)
,filter(allselected)
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.