Calculate Max Value in a Column Based on Another Column (Group)

DAX DAX Calculations
3

Hello @daniel.new,

Thank You for posting your query onto the Forum.

First of all, my apologies if this is an extremely basic question

So first and the foremost thing is, there’s absolutely no need to apologise. We as an members or experts are there to assist one another and on this forum no question is considered as a small question. We all learn when a query is posted over the Forum. So always feel free to ask your questions. :+1:

Now, coming to the solution. Below are the two measures provided for the reference. One measure calculates the “Max Days” at an individual level and other one is just written to fix the “Grand Totals”. Our expert member @Greg had already created a post on this topic about fixing the totals so below is the link of that post provided for the reference purpose as well.

Max Days = 
VAR _Selected_Value = SELECTEDVALUE( Data[PatientID] )

VAR _Max_Days = 
MAXX(
    FILTER( ALL( Data ) , 
        Data[PatientID] = _Selected_Value ) , 
    Data[Days] )

RETURN
_Max_Days



Max Days - Totals = 
SUMX(
    SUMMARIZE(
        Data , 
        Data[PatientID] , 
        "@Totals" , 
        [Max Days] ) , 
    [@Totals]
)

Below is the screenshot of the final results provided for the reference -

Final Results

I’m also attaching the working of the PBIX file as well as providing the link of the video which Sam Sir has already created over the EDNA YouTube channel on this very topic for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Max Days - Harsh.pbix (15.8 KB)

2 Likes