cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

## 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 ThirtyDWt = CALCULATE(MAX(Measurements[Weight (lbs.)]), FILTER(Measurements, Measurements[PatientID] = CurrentPt && Measurements[Measurement Date] = ThirtyD))
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
Super User

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

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!

6 REPLIES 6
Super User

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

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!

Community Champion

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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

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!

Super User

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

Regards

Phil

If I helped you, click on the Thumbs Up to give Kudos.

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.

Super User

regards

Phil

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors