The Power Apps WITH function simplifies your Power Fx formulas by allowing you to create variables and reuse them on other calculations.

This means you won’t have to repeat the same formula multiple times when you use the WITH function.

How it works

For this example, we’ll be using an application we have built that is already connected to a Sharepoint database.

We added a text label where the price of the selected item should show up.

You can use the Power Apps WITH function to populate a desired value on the text label. To apply the WITH function, use the following syntax: With(record, formula).

To use this in this application’s context, we add With({}), wherein the record is placed inside the curly braces {} .

The formula is then placed after the comma (,). The text inside the quotation marks (“”) is what appears before the value, which is preceded by &.

The value is then reflected on the text label.

Using the Lookup Function to pull up records from the database

Since the application we’re using is associated with a Sharepoint database, we’ll need to add a formula that reflects the price from the database.

To pull up records from the associated database, use the Lookup function.

“Original Price is” & Lookup(Products, Product = DropdownProduct.Selected.Value).Price & “. Final Price is” & (Lookup(Products, Product = DropdownProduct.Selected.Value).Price – ((Lookup(Products, Product = DropdownProduct.Selected.Value).Price)*0.1)

The formula above aims to pull up the price of the item selected on the drop-down control (in this case, “notebook”) from the Sharepoint database.

The formula states that the Original Price is the price value (from Sharepoint) of the selected item on the dropdown control (notebook).

You can add another formula if you want the text label to show a discounted price.

In the sample code below, we’re adding a 10% discount to the original price as the final price. We’re using the same Lookup code to pull up the price of the selected item, and then subtract 10% from it.

This formula pulls up the records from the database and calculates the discounted price from the same data pulled up using the Lookup function.

While this formula gives you accurate and real-time data from your Sharepoint database, repeating the same formula to query the same records multiple times can be resource-intensive and can eventually slow down your application.

To avoid repeating formulas to query the same records from a database multiple times, you can use the With function with the Lookup function.

Using With Function with the Lookup Function

Using the With function with the Lookup function allows you to query a record from the data base only ONCE (instead of executing the Lookup 3 times like in last example), making the code more efficient. To do this, follow the steps below:

Step 1

On the fx field, add the Lookup syntax once on your With formula and associate it with the variable Price.

Step 2

With the succeeding formula, add the variable Price instead of the whole Lookup syntax.

With a shorter and cleaner code, you pull up the price data from the database ONCE and store that information in the Price variable.

Then, you can use that variable as many times as you need without needing to execute the Lookup to query the database for that information again.