cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Qotsa Regular Visitor
Regular Visitor

Count using filters

Hi,

 

I have this measure -

 

C/F Closed = CALCULATE(DISTINCTCOUNT([ClientRef]),[Carried Forward] = 1,ClientsList[EndDate] >= DATE(2019,1,1))
 
error is:
A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
 
What I am trying to do is distinct count  ClientRef only if [Carried Forward] = 1 & ClientsList[EndDate]  >= DATE(2019,1,1))
 
Measure [Carried Forward] is 1 or 0
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
bhpage Regular Visitor
Regular Visitor

Re: Count using filters

Hi,

 

I created a little mock dataset that looks like this:

Count using filters 3.PNG

 

As you can see from the data, three Client Refs meet the criteria you've listed (1, 3, 9). I created the following calc that seemed to work for me:

 

C/F Closed = COUNTX( FILTER (FILTER ( ClientsList, ClientsList[Carried Forward] = 1 ), ClientsList[End Date] >= DATE(2019,1,1)), ClientsList[Client Ref])
 
It outputs 3, as expected:
 
Count using filters 2.PNG
 
Let me know if this works for you.
 
Thanks,
Ben

 

2 REPLIES 2
Highlighted
bhpage Regular Visitor
Regular Visitor

Re: Count using filters

Hi,

 

I created a little mock dataset that looks like this:

Count using filters 3.PNG

 

As you can see from the data, three Client Refs meet the criteria you've listed (1, 3, 9). I created the following calc that seemed to work for me:

 

C/F Closed = COUNTX( FILTER (FILTER ( ClientsList, ClientsList[Carried Forward] = 1 ), ClientsList[End Date] >= DATE(2019,1,1)), ClientsList[Client Ref])
 
It outputs 3, as expected:
 
Count using filters 2.PNG
 
Let me know if this works for you.
 
Thanks,
Ben

 

Qotsa Regular Visitor
Regular Visitor

Re: Count using filters

That's great Ben. Thanks alot.