Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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/)
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/)
Can I modify this formula somehow to now get an average response count instead of a max?
Thanks again!
Betsy
Figured it out. Thanks again for your help with this!
@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!
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 ! 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
105 | |
69 | |
67 | |
43 |
User | Count |
---|---|
148 | |
103 | |
103 | |
88 | |
66 |