Answered

Calculating various drop down numbers

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello everyone,

On my canvas app form I have 20 questions Q1-Q20.
If yes is selected from the dropdown in Q1 then a further 2 questions appear, both of which are dropdowns containing:

Q1a = 1-4
Q1b= 1-5

Q2 = Yes or No dropdown, if yes is selected a further 2 questions:

Q2a = 1-4

Q2b = 1-5

etc.

I have a single text field which needs to calculate the highest number from each Q1a and Q1b and multiply the 2, but the maximum number can only ever be 4*5 (20).

i.e. if Q1a and Q1b have 4 and 5 selected, but Q2a and Q2b also have 4 and 5 selected, the calculation should not present an answer for both.

In another scenario, there may never be 4 and 5 selected in any of the 20 questions, so it needs to work out the highest selected number from a and b questions (from the 20 questions) and present the answer.

How can I do this, the questions span across 3 screens?

Thanks

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

Mr-Dang-MSFT Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,

It sounds like you want to place limits on the min and max of the calculations that can be made. You can use the Min() and Max() functions to achieve that.

Docs: https://docs.microsoft.com/powerapps/maker/canvas-apps/functions/function-aggregates

Let's take Question 1:

Min( 4*5, Max(DropdownQ1a.Selected.Value,DropdownQ1b.Selected.Value)*2 )

Starting from the inside out, this means, "Get the larger of two numbers: the selection for Q1a or Q2b and multiply it by 2. Then compare that result with 4*5 and return the smaller of two because you don't want the number greater than 20.

I'm unclear about your second scenario--can you explain it a little bit more?
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @Mr-Dang-MSFT

Thanks for your response.
I may have not explained properly, the drop down in Q1a containing a number from 1-4 and the question in Q1b containing dropdown numbers 1-5 should be multiplied if they are the both the highest numbers selected in any of the questions from Q1a/Q1b all the way through to Q20a/Q20b.
I basically need the highest Highest number from Q1a-Q20a and the highest number from Q1b-Q20b and both them multiplied
Verified answer

Mr-Dang-MSFT Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Are your questions laid out in a gallery or is there a separate control for each question?

If you have different drop down menus for every single question, your formula for getting the highest value in each set might look like this:

Max( DropdownQ1A.Selected.Value, DropdownQ2A.Selected.Value, DropdownQ3A.Selected.Value, ... ) * Max( DropdownQ1B.Selected.Value, DropdownQ2B.Selected.Value, DropdownQ3B.Selected.Value, ... )

This means, "Get the highest value among the questions of type A and multiply it with the highest value among the questions of type B."

If you want to perform the multiplication on pairs of questions in which the selection is NOT both 4 and 5, it is best to do that with the questions laid out in a table in a gallery. Ex:

All 20 questions are in a table, TableQuestions.

Gallery1 has its Items property set to TableQuestions.

Gallery1 includes a DropdownA for question A and DropdownB for question B.

And if you wanted to find the product of questions in which 4 and 5 are NOT selected together, it might look something like this:

With( { tableFiltered: Filter( Gallery1.AllItems, Not( And( DropdownA.Selected.Value=4, DropdownB.Selected.Value=5 ) ) ) }, Max(tableFiltered,DropdownA.Selected.Value) *Max(tableFiltered,DropdownB.Selected.Value) )

Reading from inside outward, this means, "Filter the gallery of questions to show questions where 4 and 5 are not selected together and hold that in a temporary table called tableFiltered. Use that temporary table to get its highest value among A and multiply it with the highest value among B."

If I am not understanding your scenario, could you describe it with more examples?