Forum Discussion
Solved
Distinct Count filter with sum
Hello, I am a newbie trying to count Lenders w/ 4 or more certs for a year. I am trying this formula but it does not filter >3, it just gives me a straight count. Active Lenders 1 yr = CALCULA...
bcardenas Sorry, I didn't see that you have posted the data, you can try this:
Total Certificate = SUM ( Certificates[certified] )Active Lenders 1 yr = COUNTROWS ( FILTER ( SUMMARIZE ( Certificates, Certificates[InstitutionID], 'Date'[Year] ), [Total Certificate] > 3 ) )Model
lbendlin I would prefer to not have the year filter in the measure.
Data -
| InstitutionID | certified | cert_date |
| 1 | 1 | 1/1/2020 |
| 1 | 1 | 2/5/2020 |
| 1 | 1 | 2/8/2020 |
| 1 | 1 | 3/10/2020 |
| 2 | 1 | 1/20/2020 |
| 2 | 1 | 1/26/2020 |
| 2 | 1 | 3/1/2020 |
| 3 | 1 | 4/1/2002 |
| 1 | 1 | 1/1/2019 |
| 1 | 1 | 2/5/2019 |
| 1 | 1 | 2/8/2019 |
| 1 | 1 | 3/10/2019 |
| 2 | 1 | 1/20/2019 |
| 2 | 1 | 1/26/2019 |
| 2 | 1 | 3/1/2019 |
| 2 | 1 | 3/2/2019 |
| 3 | 1 | 4/1/2019 |
Outcome -
| YEAR | Instiution Count | Inst > 3Certs |
| 2020 | 3 | 1 |
| 2019 | 3 | 2 |
lbendlin
Super User
Super User"I would prefer to not have the year filter in the measure."
That is impossible, as you want the year grouping in your output.
Note - there is a typo in your sample data . I guess you meant 4/1/2020 ?
Here is a version that uses AntrikshSharma 's approach but does not require an external measure, and doesn't require the redundant column.
GT3 =
var s = summarize(Certificates,Certificates[InstitutionID],Dates[Yearcode],"ct",count(Certificates[cert_date]))
var c = countrows(filter(s,[ct]>3))
return c