Forum Discussion
Allisond
Advocate II
Advocate IIUsing VAR if returns BLANK then next Calculation in Switch True
Good Afternoon,
This is a small sample of what I am trying to do to get a value closest to a date. I have multiple VAR for -29 days, -31 days etc..
Please see below. My first Result for "ThirtyDWt" works perfectly, however, if it results BLANK, I would like the SWITCH (TRUE()) to move on to the next else calculation. I am only getting a -30DWt (if one is there) or a BLANK result. It does not reach to the second "Else" to look for the -31 as I had hoped. Can anyone help me with what I am doing wrong?
30 Day Measurement=
VAR CurrentPT = Measurements[PatientID]
VAR ThirtyD = DATEADD(Measurements[Measurement Date],-30,DAY)
VAR ThirtyDWt = CALCULATE(MAX(Measurements[Weight (lbs.)]), FILTER(Measurements, Measurements[PatientID] = CurrentPt && Measurements[Measurement Date] = ThirtyD))
VAR ThirtyMin1 = DATEADD(Measurements[Measurement Date],-31,DAY)
VAR ThirtyMin1Wt = CALCULATE(MAX(Measurements[Weight (lbs.)]), FILTER(Measurements, Measurements[PatientID] = CurrentPt && Measurements[Measurement Date] = ThirtyMin1))
VAR ValidRow =Measurements[Valid Wt Change Row] = "True"
Return
SWITCH(TRUE(),
ValidRow, ThirtyDWt, --- WORKS
ValidRow && ThirtyDWt = BLANK(), ThirtyMin1Wt, ----Does NOT WORK
BLANK()) ---WORKS
Can anyone see what I am missing?
Thank you for taking the time to take a look at this!
Hi Allisond
It's abit complicated to understand but looking at your SWITCH, the order of the logic needs changing. If ValidRow is True then the 2nd line (ValidRow && ThirtyDWt) will never be evaluated
Try this
Return SWITCH( TRUE(), ValidRow && ThirtyDWt = BLANK(), ThirtyMin1Wt, ValidRow, ThirtyDWt, BLANK() )Regards
Phil
6 Replies
- PhilipTreacy
Super User
Hi Allisond
It's abit complicated to understand but looking at your SWITCH, the order of the logic needs changing. If ValidRow is True then the 2nd line (ValidRow && ThirtyDWt) will never be evaluated
Try this
Return SWITCH( TRUE(), ValidRow && ThirtyDWt = BLANK(), ThirtyMin1Wt, ValidRow, ThirtyDWt, BLANK() )Regards
Phil
PaulDBrownCommunity Champion
Could you please provide a sample dataset or PBIX file? And a depiction of what you are trying to calculate
AllisondAh! So I have a number of these parameters. I will try to plug those in this am and use the original last and give it a try this am and get back. Thank you!
- PhilipTreacy