Distinct Count filter with sum

[bcardenas]

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 = CALCULATE(

DISTINCTCOUNT(Certificates[institutionID]),

FILTER(Certificates,(SUM(Certificates[Certified]) > 3)))

[AntrikshSharma]

@bcardenas Try this:

Active Lenders 1 yr =
CALCULATE (
    DISTINCTCOUNT ( Certificates[institutionID] ),
    FILTER (
        ALL ( Certificates ),
        CALCULATE ( SUM ( Certificates[Certified] ) ) > 3
    )
)

[bcardenas]

@AntrikshSharma  when I try this solution I do not get an error but the measure is blank when pulled into a report.

[AntrikshSharma · Accepted solution]

@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

[bcardenas]

@AntrikshSharma  Much thanks, this is exactly what I needed.

[amitchandak]

@bcardenas , Try like

Active Lenders 1 yr = countX(filter(summarize(Certificates,Certificates[Lenders],"_1", CALCULATE(
DISTINCTCOUNT(Certificates[institutionID]))),[_1]>3),[Lenders])

[lbendlin]

you are missing the year filter, and the ALL* context modifier.

Provide some sample data and expected outcome.

[bcardenas]

@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]

"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