First of all, my apologies if this is an extremely basic question, but I’m fairly new to Dax, so any help would be much appreciated. I have a table with many columns, but the calculation I need only requires two specific columns. Here is what I’m trying to calculate:
Column 1 - “PatientID”: (multiple rows have the same patientid)
1
1
1
2
2
3
3
3
3
Column 2 - “Days”: (For every “PatientID” row there is another column with associated “Days”.)
I am looking to calculate the Max days for each “PatientID”. Then, I want to Sum all of the Max days that were calculated for each PatientID. I would prefer to do this via Measure instead of Column, unless there’s value in creating a new column.
@daniel.new If PatientID 1 has 3 different MAX days then there must be some other columns that will introduce the uniqueness in the filter context, otherwise PatientID 1 will always return 31 everywhere. Can you please share the PBIX file?
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.
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 -
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.
Thank you SO much! This was perfect. I’ve quickly determined that I need to spend some time learning more about VAR and the “X” functions (SUMX, AVERAGEX, MAXX, etc.). I’m going to study up on those and hopefully that will get me up to speed a bit more. Again, thank you very much for your help!
