Count using filters

[Qotsa]

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

[Anonymous · Accepted solution]

Hi,

I created a little mock dataset that looks like this:

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:

Let me know if this works for you.

Thanks,

Ben

[Qotsa]

That's great Ben. Thanks alot.