Forum Discussion

Betsy's avatar
Betsy
Icon for Helper IV rankHelper IV
9 years ago
Solved

Max count of duplicate values?

Hi All,   I have a table that looks something like this, which displays the responses students have sent to questions. I'm looking to find a MAX response count (by student) that can be filtered by ...
Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    Hi Betsy

     

    A general measure to give you the max response count per student is:

    Max Response Count =
MAXX ( VALUES ( YourTable[Student ID] ), CALCULATE ( COUNTROWS ( YourTable ) ) )

    (replace YourTable with actual table name).

    This will respond to filters.

     

    If you also want a measure for the student with the max response count, it would be:

     

    Student with Max Response Count =
FIRSTNONBLANK (
    TOPN (
        1,
        VALUES ( YourTable[Student ID] ),
        CALCULATE ( COUNTROWS ( YourTable ) )
    ),
    1
)

    (pattern taken from here: http://www.sqlbi.com/articles/alternative-use-of-firstnonblank-and-lastnonblank/)

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
9 years ago

Hi Betsy

 

A general measure to give you the max response count per student is:

Max Response Count =
MAXX ( VALUES ( YourTable[Student ID] ), CALCULATE ( COUNTROWS ( YourTable ) ) )

(replace YourTable with actual table name).

This will respond to filters.

 

If you also want a measure for the student with the max response count, it would be:

 

Student with Max Response Count =
FIRSTNONBLANK (
    TOPN (
        1,
        VALUES ( YourTable[Student ID] ),
        CALCULATE ( COUNTROWS ( YourTable ) )
    ),
    1
)

(pattern taken from here: http://www.sqlbi.com/articles/alternative-use-of-firstnonblank-and-lastnonblank/)

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Anonymous

    Related to http://community.powerbi.com/t5/Desktop/TOPN-Function-Question-Comment/m-p/109699

    Its quite obvious now just look at OwenAuger's second formula and the link to the SQLBI article

    The easiest way to take care of the TIES => wrap in FIRSTNONBLANK ( )

    I now like my CONCATENATEX solution better

    However if I had thought about wrapping in FIRSTNONBLANK I may not have even tried to list all tied items! :smileyhappy:

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Sean,

      Good point.

       

      But it depends on your business requirement too - is it OK or necessary to just randomly (alphabetically) choose just one of several equal-ranked items?  An Olympic medallist whose name starts with Z or W might say no - https://en.wikipedia.org/wiki/List_of_ties_for_medals_at_the_Olympics ! :)

  • Betsy's avatar
    Betsy
    Icon for Helper IV rankHelper IV
    9 years ago

    Thanks a million OwenAuger! Works perfectly.

  • Betsy's avatar
    Betsy
    Icon for Helper IV rankHelper IV
    9 years ago

    OwenAuger

     

    Can I modify this formula somehow to now get an average response count instead of a max?

     

    Thanks again!

    Betsy

    • Betsy's avatar
      Betsy
      Icon for Helper IV rankHelper IV
      9 years ago

      Figured it out. Thanks again for your help with this!