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 = CALCULATE(
DISTINCTCOUNT(Certificates[institutionID]),
FILTER(Certificates,(SUM(Certificates[Certified]) > 3)))
  • 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

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

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

     

    Provide some sample data and expected outcome.

    • bcardenas's avatar
      bcardenas
      Frequent Visitor
      6 years ago

      lbendlin  I would prefer to not have the year filter in the measure.

       

      Data - 

      InstitutionIDcertifiedcert_date
      111/1/2020
      112/5/2020
      112/8/2020
      113/10/2020
      211/20/2020
      211/26/2020
      213/1/2020
      314/1/2002
      111/1/2019
      112/5/2019
      112/8/2019
      113/10/2019
      211/20/2019
      211/26/2019
      213/1/2019
      213/2/2019
      314/1/2019

       

      Outcome - 

      YEARInstiution CountInst  > 3Certs
      202031
      201932
      • lbendlin's avatar
        lbendlin
        Super User
        6 years ago

        "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

         

  • amitchandak's avatar
    amitchandak
    Super 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])

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    6 years ago

    bcardenas Try this:

     

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

     

    • 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
        Community 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