cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Betsy Member
Member

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 date.

 

Student ID       Package ID      Date Sent  text

1                       101                  9/1/16        yes

2                       101                  9/1/16        yes

2                       102                  9/2/16        1

4                       101                  9/1/16        no

4                       102                  9/2/16        1

4                       103                  9/3/16        5

 

I already have an overall MAX measure for responses, that comes from a summarized calculated table. But the summarized table of course no longer has dates attached (it gives me a response count for each student overall), so it isn't filtering.

 

What I need is to be able to show the following:

 

From 9/1/16-9/3/16 the MAX response count is 3. For 9/1/16 the MAX response count is 1. Basically, I want to identify the Student ID that has the most duplicates, and count the number of those duplicates. 

 

Thanks for any help!

 

Betsy

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Max count of duplicate values?

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/)



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




6 REPLIES 6
Highlighted
Super User
Super User

Re: Max count of duplicate values?

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/)



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Max count of duplicate values?

@Steve_Wheeler

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! Smiley Happy

Steve_Wheeler Established Member
Established Member

Re: Max count of duplicate values?

@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 ! Smiley Happy

Betsy Member
Member

Re: Max count of duplicate values?

Thanks a million @OwenAuger! Works perfectly.

Betsy Member
Member

Re: Max count of duplicate values?

@OwenAuger

 

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

 

Thanks again!

Betsy

Betsy Member
Member

Re: Max count of duplicate values?

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