cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LaelH
Advocate III
Advocate III

Dynamic or parameterised calculations

Hi All,

Hoping someone smart out there has a solution for me  😣.

I am wanting to use 'dynamic' calculations in my app, or  to ‘parameterise’ calculations   i.e. allow a user to configure their own user-specific calculation (which I will store in a db against their user name), and then apply that user-specific calculation in PowerApps, instead of forcing the same generic calculation for everyone. 

What I am battling with is getting PowerApps to ‘evaluate’  or  ‘execute’ or ‘run’ the calculation which I have stored.

 

As a proxy for this scenario I built the very simple Screen3 below.

I want to be able to save any 'valid' calculation as a text string in “My Calculation”. 

 

Then I want “My Answer” to reference that calculation saved in “My Calculation”, and return the ‘evaluated’ result i.e. 6 ……. and not the text string as it is doing now. Any ideas how I can do that?

 

(In my real world scenario the calc is saved in the db and pulled into a collection in PowerApps. I then read the calc from a field in the collection. 'My Calculation' in the screen below is a proxy for that field in my collection)

 

LaelH_0-1612531490287.png

Any ideas will be most appreciated.

Regards

Lael

2 REPLIES 2
rubin_boer
Super User
Super User

hi @LaelH 

 

Here is an idea you can work with (i hope :)).

 

The calc is Sum(1,2,3)

Extract the calc from the table and set it to a variable which will be something like this - Set(_Calc, "Sum(1,2,3")) 

 

First let's get the calculation to be performed which we will pass to a switch statement based on how complex your calculations are (i won't go into the switch function).

"Mid(_Calc,1,Find(""("",_Calc)-1)" this will result in Sum

 

Now lets get the variables/elements

As we want to do some calc on it it will be a collection

1. "Mid(_Calc,Find(""("",_Calc))" will result in (1,2,3)

2. Set(_vars,Mid(_Calc,Find("(",_Calc)))

3. Set(_Raw,(Replace(_vars,1,1,""))); Set(_Raw,(Replace(_Raw,Len(_Raw),1,""))); Set(_compute,Split(_Raw,",")) will give me a collection.

4. I now get the sum of the collection, Sum(_compute,Result) which will give me 6

this is illustrated below

rubin_boer_0-1612633007754.png

 

 

 

 

 

 

 
 
LaelH
Advocate III
Advocate III

@rubin_boer 

Very clever! I like the way your brain works. And the solution absolutely works for that calc.

 

But sadly not a solution for me. Your offered solution is 100% unique to the calc "Sum(1,2,3)".  However, I need the solution to be generic/dynamic enough to handle any calc presented. So if I changed the calc to "Sum(1,2,-65,4,-3)", then it should still work. Other calc examples might be "TotalAmount * VATPercent" (where TotalAmount and VATPercent are variables set previously )  or "Length * Width * Height".  In essence I need to be able to pass a valid calculation as a string to some sort of PowerApps "evaluation" engine and get the result returned. I believe it is not possible yet, but is logged as an idea here. 

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Eval-function/idi-p/41079

Please vote for it to push it up the queue?

Thanks for trying. Really appreciate it.

Regards

Lael

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,981)