@Johg_Ananda Okay, here’s a more expanded reply. I’d illustrate it with a formula I wrote yesterday:
RunActions(
[DB Scenario metric uplifts].Filter(
Scenario = thisRow
AND [Value driver].IsNotBlank()
AND thisRow.[Select value drivers].Contains([Value driver]).Not()
).DeleteRows(),
If(
thisRow.[Select value drivers].IsNotBlank() AND [DB Scenario metric uplifts].Filter(Scenario = thisRow AND [Value driver].IsBlank()).Count() = 0,
Sequence(0, 5).FormulaMap(
[DB Scenario metric uplifts].AddRow(
Scenario, thisRow,
Year, CurrentValue,
Ramping, [Template ramping].Nth(CurrentValue + 1)
)
),
_Noop()
),
If(
thisRow.[Select value drivers].IsBlank(),
_Noop(),
thisRow.[Select value drivers].FormulaMap(
RunActions(
thisRow.ModifyRows(thisRow.[_Current value driver], CurrentValue),
thisRow.[_Current value driver].[Metrics Impacted].FormulaMap(
RunActions(
thisRow.ModifyRows(
thisRow.[_Current metric impact], CurrentValue
),
thisRow.[_Add current metric impact]
)
)
)
)
)
)
As I said above, I use best judgement when deciding whether to break down into separate lines or not. The goal is formula readability in the constraints of a pretty narrow window. If breaking down into separate lines makes reading the formula harder and not easier, it would make sense to not do that, right?
Example of that:
In the DeleteRows formula, I’m breaking down the conditional in Filter into separate lines:
This is what I’m usually doing BTW with long boolean expressions with lots of
ANDs.
However, further down I’m not breaking a similar long filter expression into separate lines:
This is easier for me to read because I know that to jump to the
if expr=true clause, I don’t need to scan the formula line by line, but can simply jump the block of text that overflows, right to the next indented line. Whereas if I broke down the expression like this:
it would require me to scan each line, looking for a comma.
My rules of thumb:
-
RunActions() — always break down and indent, each action starting on a new line. If actions are too long, consider adding empty lines between actions.
-
FormulaMap() — always break down and indent.
-
Filter() — keep inline if the condition is simple (only one operation e.g. X = Y or X.Contains(Y).Not()) or breaking down would only make overall readability worse (e.g. when surrounded by lots of other code), otherwise break down and indent.
-
If() — keep inline if the condition is simple and the true/false values are simple as well, otherwise break down and indent.
-
Switch() — always break down and indent. Write the expression on a separate line, then case and value pairs each on the same line if the case is just a value. Otherwise consider using SwitchIf().
-
SwitchIf() — always break down and indent. Write expressions and calculations on separate lines. Consider adding empty lines between expression/calculation pairs if either part gets broken down into multiple lines itself:
SwitchIf(
thisRow.[New lines in this row].Not(),
"",
thisRow.[Cohort type] = "Adds",
thisRow.Scenario.[Metric uplifts].Filter(
Metric = [New Lines/Services]
AND Year = thisRow.[Year index]
).[Ramped uplift].Sum(),
thisRow.Scenario.[Metric uplifts].Filter(
Metric = Winbacks
AND Year = thisRow.[Year index]
).[Ramped uplift].Sum()
)
-
AddRow(), ModifyRows() — only consider keeping inline if you’re setting one column->value pair, otherwise break down into separate lines, with column and its value being on the same line:
Also if one of the paths in an If() is long (i.e., takes more lines than a formula editor can display), and the condition is easy to swap (e.g. just replacing .IsBlank() with .IsNotBlank(), consider writing a shorter path, e.g. _Noop(), first, so that you can see it while still seeing the condition:
Looking forward to some more value bombs!
