Counting where 4 occurrences of an event occur in for a field.

[Mickfenwick]

I have a table in Power Bi which lists a student ID, event names, event dates and a day number conversion

Student ID	Event Name	Event Date	Day Number
1111	Outstanding Work	22/01/2021	22
1111	Outstanding Work	22/01/2021	22
1111	Outstanding Work	22/01/2021	22
1112	Outstanding Work	22/01/2021	22

I would like to end up with a distinct count of the number of students achieving 4 outstanding, 3 outstanding, 2outstanding, 1 outstanding per day using the number of times the student ID occurs with the event name on the event date / day number to produce a chart from Power Bi as follows in this mocked up example in Excel

I would be grateful for any advice or pointers on how to achieve this.  Many thanks for any help offered.

[m3tr01d]

Hello!

I added more data for my file. I use two measures and a disconnected table to store the 4 values for 
Outstanding number ( 1, 2, 3, 4).
First measure is simple :

--To count the number of values for Student ID
NbStudentValue = COUNTA( Event[Student ID] )

 
The second one is :

NbStudentOutstanding = 
VAR _SelectedOutstandingNumber = SELECTEDVALUE( OutstandingTable[Number] )
RETURN
COUNTROWS(
	FILTER(	
		VALUES( Event[Student ID]),
		[NbStudentValue] = _SelectedOutstandingNumber
	)
)

What it does : 
- Store the value for outstanding number from filter context
- Iterate for all the distinct Student using FILTER
  - For each Student, count the values of student ID and test if equal to Outstanding Number

This return a table of valid Student and we count the rows in this table
I put a filter for Outstanding Event in the Filter for my visual.
 https://drive.google.com/file/d/1nGZuKGQOF1LhXJzF5yzp2ir4xE0GF29I/view?usp=sharing

Have a good day.

[nandic · Accepted solution]

Hi @Mickfenwick ,

Attached pbix file with results based on my demo data.
In table i added one custom column to calculate number of outstandings:

Number of outstandings = CALCULATE(COUNTROWS('Table'),'Table'[Event Name]="Outstanding Work") & " outstandings"

I used this on chart in "legend" section.

After that create a measure:

Outstanding Students =

CALCULATE(DISTINCTCOUNT('Table'[Student ID]),FILTER('Table','Table'[Event Name]="Outstanding Work"))

Drag this measure on chart in "Values" section.

This is final result (chart on left shows total, chart on right shows total divided by number of outstandings).

Regards,
Nemanja Andic

[Mickfenwick]

Thank you so much.  This solution was perfect.  Many thanks.