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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Allisond
Advocate II
Advocate II

Using 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!
1 ACCEPTED SOLUTION
PhilipTreacy
Super User
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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

6 REPLIES 6
PhilipTreacy
Super User
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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


PaulDBrown
Community Champion
Community Champion

@Allisond 

Could you please provide a sample dataset or PBIX file? And a depiction of what you are trying to calculate





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Ah!   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!

@Allisond 

OK no worries.  Yes, the order of the lines is important.

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


I was not leveraging the logic correctly thinking about the "true" and how if false then moves to the next.   Adjusted my statements to 

 

Valid Row && ThirtyDWt <> BLANK(), ThirtyDWt,

Valid Row && ThirtyDMin1Wt <> BLANK(), ThirtyDMin1Wt,

Etc.

 

And this seemed to take care of it.   Thank you for your help in getting me to think this through.

Hi @Allisond 

If my answers helped to solve your problem please mark my answer(s) as the solution.

regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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