Issue with Filter and SwitchIf expression

[Michael_Paul]

Hello Coda Community!

I am fairly new to Coda (my first post) and trying to understand why the below expression is returning “False” instead of the expected “20 - 30”.

I am probably looking right at the problem but do not understand Coda well enough to recognize it. I suspect the expression , as written, is not allowing the SwitchIf function to “see” the filtered row containing “Test Name 1” properly and instead is a list of values in the condition portion of the arg pairs.

Any guidance would be appreciated.

Below is the expression with comments for clarity.

[Jasmine_B]

Hey @Michael_Paul, thanks for reaching out to the community and glad to hear you’re getting into Coda

It’s a bit hard to totally tell seeing the formula abstracted from the larger setup like this, but here’s how I’d approach troubleshooting this.

Since your formula has a bunch of moving parts (a filter, a switchif, etc), I’d recommend breaking it down by sections to figure out which part is problematic. Start with each chain in the formula (e.g. the filter formula portion, then the next portion) and see what values are returned. This’ll show you what the output is at each individual part of the formula. If things look like they’re returning what you’d expect, slowly add back the next chain of your formula, and so on. This’ll help pinpoint which portion of the formula is unable to be executed, and that’ll make it a lot easier to figure out what needs editing.
​
One of my colleagues has a really handy doc here with advice on troubleshooting big formulas that I’d also recommend taking a look at: Good habits: how to write and troubleshoot big formulas

Hope that helps!

[Michael_Paul]

Hi Jasmine_B,

Thank you for the reply and suggestion! I actually came across the troubleshooting guide you suggest which did prove helpful. After changing a few things the formula worked as needed.

Posting the copy of the final version should it be helpful to anyone.

Thanks again.

[Paul_Danyliuk]

Hey @Michael_Paul, welcome to the community!

You’re using SwitchIf() wrong — particularly, you’re trying to chain it onto a result of the filter, which is not how it works. The syntax isSwitchIf(expr1, val1, expr2, val2,...) and by chaining it onto a filter, you’re essentially passing the result of the filter (the list of rows) as the first argument expr1, shifting all other arguments by one i.e. your formula is equivalent to:

SwitchIf(
  Table.Filter(...) /* = true */, Table.IsBlank() AND etc // - this what gives you the `true`,
  "Both Are Blank", Table.IsBlank()...
)

I understand it, you want to get one row by the filter, and then run this formula on its values?
Then the way to do this is like this:

[DB Blood Etc].Filter(
  [Used Name] = "Test Name 1"
).First().WithName(FoundRow,  // First() to unwrap the list; WithName to save that row to a variable
  SwitchIf(
    FoundRow.FnctMin.IsBlank() AND ..., "Both are blank",
    FoundRow.FnctMax.IsBlank() AND ..., Concatenate(">", ...)
    // etc.
    // Note that we read `FnctMin` from `FoundRow`, not from the whole `DB Blood Values`.
    // That would return the whole column
  )
)

Also could be a good idea to just calculate your switchif within a column of that table for each row using thisRow and then just return the

[DB Blood Etc].Filter(
  ...
).ThatColumn

(P.S. Oh blind me, I missed that you already did that)

FYI, chaining a function is almost always equivalent to passing the value before the dot as the first argument to the function, i.e. Something.Function(Arg) is equivalent to Function(Something, Arg). A few notable exceptions are Format() that ignores chained value, and for some reason Find() (perhaps it’s like that in Excel, hence the Find(needle, haystack) but haystack.Find(needle).

---

Oh, I missed that you already solved it. Well, hopefully my answer is still helpful and you can understand CFL better now