Forum Discussion

bcardenas's avatar
bcardenas
Frequent Visitor
6 years ago
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...
  • AntrikshSharma's avatar
    AntrikshSharma
    6 years ago

    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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

bcardenas , Try like

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