Filtering to get the same results multiple times in a formula.... Is there an easier way?

I find myself using formulas fields with formulas such as:

If(table.Filter([conditionsABCD]).isNotBlank(), table.Filter([conditionsABCD]).value1.Sum()/table.Filter([conditionsABCD]).value2.Sum(),"")

It seems overly complex (and computationally expensive?), to perform the same filter multiple times just to use the resulting list in a number of ways.

Surely there’s an easier way?

It would be nice to have behavior more like setting a variable to a query result and then re-using it.

As an example, I have a table, Crop Types, with a formula field, Typical Yield that looks at “planting amount” and “yield amount” on Plantings of the crop type. Some crop types have not been planted or have been planted but not harvested, that is, there is no yield data. But I do not want to throw an error in those cases; I want the field to remain blank.

Hi Paul,
Can you clarify what is the final calculation you are trying to achieve? I’m confused on what value1 and value 2 are. I created a mock table of data but I’m not sure what calculations you want in order to create a formula for it.

1 Like

You’re looking for “WithName()”

1 Like

Great thanks!!

In case it’s not transparent to others how this relates, before I had:

If(table.Filter([conditionsABCD]).isNotBlank(), table.Filter([conditionsABCD]).value1.Sum()/table.Filter([conditionsABCD]).value2.Sum(),"")

Now it’s:

WithName(table.Filter([conditionsABCD]), results, If(results.IsNotBlank(), results.value1.Sum()/results.value2.Sum(),""))

Much better!

1 Like

value1 and value2 were just meant to stand for any fields that I might want to use from the same set of rows or list of results. In my example the values were planting amounts and yield amounts. I ultimately wanted the sum of the yield amounts divided by the sum of the planting amounts to get an average yield for the crop type, but only where there was data to be used.

But a similar thing happens to me all the time in different situations. The main point is that I was performing the same filtering repeatedly to use the same results in multiple ways. WithName() fixes that.

Thanks for the help!

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.