Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
Allisond
Advocate II
Advocate II

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 2
mahoneypat
Microsoft Employee
Microsoft Employee

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,
Pat




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


It totals row by row this way, but my column total is 0.  Is there something else I need to add?

 

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors