Forum Discussion
Measure Not Calculating Totals
I have a Measure that identifies pts with a significant wt loss. It works correctly row by row, however. I need the total rows and the total number of distinct patients.
MEASURE 1 (works as intended):
30d SignWtLoss =
VAR Pt = SELECTEDVALUE(MSTR_Patient[PatientID])
VAR MaxDate = MAX(Dates[DayDate]) - 35
VAR MinDate = MAX(Dates[DayDate]) - 25
VAR Change = CALCULATE(MAX(Weights_20190901[Lbs.]), DATESBETWEEN(Dates[DayDate], MaxDate, MinDate))
VAR SamePTChange = CALCULATE(MAXX(FILTER(Weights_20190901,Weights_20190901[PatientID] = Pt),Change))
VAR WeightVar = SUMX(Weights_20190901,Weights_20190901[Lbs.])
VAR FirstVisDate = MIN(Dates[DayDate])
VAR LastAdmRtnDate = CALCULATE(MAX(Weights_20190901[Admit or Rtn Wt Date]),Dates[DayDate] <= FirstVisDate)
VAR Day30MAX = SWITCH(TRUE(), WeightVar = BLANK(),BLANK(),
LastAdmRtnDate >MAX(Dates[DayDate]) - 30, BLANK(), SamePTChange)
VAR WtLosslbs = Day30MAX - WeightVar
VAR WtLossPercent = DIVIDE(WtLosslbs,Day30MAX, BLANK())
RETURN
SWITCH(TRUE(),
WtLossPercent >= .05, 1 , 0)
MEASURE 2 (not working as intended):
30dWtLoss Distinct Pts =
SUMX(SUMMARIZE(MSTR_Patient, MSTR_Patient[PatientID],
"SignWtLoss",[TEST MEASURE 30d SignWtLoss PTS]),DISTINCTCOUNT(MSTR_Patient[PatientID]))
My result is the correct patients with wt loss, however I am getting all the rows (so if a pt had many wts that were considered significant is not counting each unique patient. Seems I am doing something wrong with Measure #2 to get the distinct count, but for the life of me I can't figure out what it is.
Would really appreciate any help.
Thanks!
2 Replies
Please try this measure expression instead. It references your existing (working) measure. You should get a count of 1 for each patient that meets the criteria.
30dWtLoss Distinct Pts =SUMX(SUMMARIZE(MSTR_Patient, MSTR_Patient[PatientID]), [30d SignWtLoss])Regards,PatIt totals row by row this way, but my column total is 0. Is there something else I need to add?
