Forum Discussion
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
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
2 Replies
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