Get Value from Min and Max date in Power Bi - Group by

Question

I am working with a database of surgeries performed.

The database contains in each row the patient's id, date of surgery and surgery performed.

A patient can have more than one surgery and this surgery can be different.

What I am trying to obtain is the number of surgeries performed by each patient, what was the first surgery and the last surgery performed.

This is the original database example:

patient	date	surgery	id
A	01-01-22	Eyes	1
B	01-01-22	Mouth	2
C	01-01-22	Nose	3
A	01-05-22	Mouth	4
C	01-06-22	Eyes	5
B	01-07-22	Mouth	6
A	01-12-22	Nose	7

I need the following output:

patient	number of surgeries	First Surgery	Last Surgery
A	3	Eyes	Nose
B	2	Mouth	Mouth
C	2	Nose	Eyes

I'm using the following Dax's expression:

N_Surgery = GROUPBY(Surgery, Surgery[patient_id], 
                           "N_Surgery", COUNTX(CURRENTGROUP(), Surgery[id]))

But i have no idea how to get the last and first surgery.

This is what I am looking to replicate using an example of R code:

Summarise <- DF%>%
                group_by(patient_id)%>%
                summarise(N_Surgery = n(),
                          First = surgery[which.min(date)],
                          Last = surgery[which.max(date)])
                              

Thanks in advance!

Answer 1

Table:

Code:

Table 2 = 

ADDCOLUMNS(
    SUMMARIZE('Table', 'Table'[patient]),
    "Number of Surgeries", CALCULATE(COUNTROWS('Table')),
    "First Surgery", 
        VAR a = CALCULATE(MIN('Table'[date])) 
        RETURN CALCULATE(MIN('Table'[surgery]), 'Table'[date] = a),
    "Last Surgery", 
        VAR b = CALCULATE(MAX('Table'[date])) 
        RETURN CALCULATE(MIN('Table'[surgery]), 'Table'[date] = b)
)