#PowerPlatformTip 43: 'With function'

TL;DR: With({name: value, ...}, Formula) gives parts of a formula a name that only exists inside that formula. Nest a second With when one named value depends on another, as in the Haversine distance below.

💡 Challenge

Complex calculations in Power Apps quickly turn into one long line where the same sub-expression appears several times. The Haversine formula for the distance between two coordinates is a good example: converted latitudes and longitudes show up again and again, and nobody can read the result a month later.

✅ Solution

The With function evaluates a formula for a single record. Pass an inline record of named values, and use those names in the formula. The names act like local variables confined to the With, without Set or UpdateContext.

🔧 How It’s Done

1. Name the inputs

With(
    { radius: 10, height: 15 },
    Pi() * radius * radius * height
)
// Result: 4712.39 (volume of a cylinder)

2. Nest a second With for values that depend on the first

🔸 Fields of the same record can’t reference each other. If a needs lat1 and lat2, define it in an inner With. All names of the outer With stay available inside.

3. Full example: Haversine distance Zurich to Bern

With(
    {
        R: 6371,                       // earth radius in km
        lat1: Radians(47.3769), lon1: Radians(8.5417),   // Zurich
        lat2: Radians(46.9480), lon2: Radians(7.4474)    // Bern
    },
    With(
        {
            a: Power(Sin((lat2 - lat1) / 2), 2) +
               Cos(lat1) * Cos(lat2) * Power(Sin((lon2 - lon1) / 2), 2)
        },
        Round(2 * R * Atan2(Sqrt(1 - a), Sqrt(a)), 1)
    )
)
// Result: 95.5 (km)

🔸 Power Fx Atan2(X, Y) takes the x coordinate first, so Atan2(Sqrt(1 - a), Sqrt(a)) is the usual atan2(√a, √(1−a)) of the Haversine formula.

🔸 Replace the fixed coordinates with Location.Latitude, Location.Longitude or the columns of a gallery row (ThisItem.Latitude) to make it dynamic.

4. Hold a function result for the next step

🔸 With also keeps the record returned by Patch or Match long enough to use it:

With(
    Patch(Orders, Defaults(Orders), { Status: "New" }),
    Notify("Order " & ID & " created")
)

🎉 Result

Inputs, intermediate values and the final calculation are separated and named. The formula reads top to bottom, and a change to an input happens in one place.

🌟 Key Advantages

🔸 Readable: each part of a complex formula has a name.

🔸 No side effects: the names exist only inside the With, nothing else in the app can change them.

🔸 Works everywhere: in data properties like Text or Items as well as in behavior formulas like OnSelect, where Set would be the only alternative.

ℹ️ Tip: When you use With on a data source, the result is held in memory and is no longer delegated. Filter the data source first and only then name the result.

🎥 Video Tutorial

🛠️ FAQ

1. Can I nest multiple With functions within each other?

Yes. Nesting is the way to go when one named value depends on another, because fields of the same record can’t reference each other. As long as names don’t conflict, all outer names are available in the inner With.

2. What’s the difference between With function and Set function for variables?

With creates named values that only exist inside its formula and works in any formula, including data properties. Set creates a global variable that lives in the whole app and only works in behavior formulas such as OnSelect.

3. Can I use With function with complex data types like collections or records?

Yes. The named values can hold records and tables, and the first argument can be any record, for example the result of LookUp, Patch or Match.

🔗 Related Tips

🔸 #PowerPlatformTip 44: ‘Calculate the Distance’ - a real-world example that uses With to keep the Haversine formula readable.

🔸 The Big 5: Variables and Parameters in PowerApps - see how With compares to Set, UpdateContext and the other core variable functions.