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

bcardenas's avatar
bcardenas
Frequent Visitor
6 years ago

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

AntrikshSharma's avatar
AntrikshSharma
Icon for Community Champion rankCommunity Champion
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