cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Prodigy
Post Prodigy

CountIf and Sum based on the Combo box selection or Dropdown

Hi,

Need help in building the solution for one of my apps where I am trying to get the Sumif function with a combination of Search and Combobox result.

I have a label where I am trying to get the result in a number.

 

akbarmahfuzalam_0-1614094659718.png

 

Challenges is when i am trying to call the Dropdown in the formula itself, not capturing the Countif function. The result is shown as zero.

But when I placed the hard code input its giving me the result. 

 

akbarmahfuzalam_1-1614094936485.png

 

 

 

 

10 REPLIES 10
Frequent Visitor

Hi @akbarmahfuzalam ,

 

If I understand your query correctly, you want to filter the collection on the column name selected from the dropdown?

 

Dynamic column filtering is not supported in PowerApps at the moment. If the number of dropdown choices are limited, your best option is to write an If condition for each selection (or use Switch case). Other option as suggested by @RezaDorrani here is to write a flow to perform the query and send the result back to the PowerApp.

 

Regards

Vineet

@VineetDesai 

 

Thanks for the Reply. Have checked the videos for Raza D. I find a bit complicated code. Need a simpler solution for this.

Since I am new to this Platform. 

 

Super User II
Super User II

@akbarmahfuzalam ,

Unfortunattely, what are you trying to do is not working in PowerApps Environment. The CountIf() function expects a "literal" column name, not a string value.

So, my proposal is to transfer your calcutation to a PowerAutomate flow called on OnChange() property of the comboBox where a query inside the GetItems action is more flexible.

 

Hope it helps !

 

Post Prodigy
Post Prodigy

@gabibalaban 

If you could share the sample video or how to go about the flow. That would really be helpful for me to develop this.

 

Post Prodigy
Post Prodigy

@gabibalaban 
Have tried to create a flow. Let me know what all I am missing and what all changes do I need to do.

akbarmahfuzalam_0-1614106484650.png

 

 

 

@akbarmahfuzalam ,

Yes, you are on the right track. 

This is what I was thinking about (of course you need to adapt to your needs using 3 parallel branches, for each of Efficient/Trained/Not Trained, please use single qoutes for strings) . Use parallel branches to reduce the flow running time.

gabibalaban_1-1614108453193.png

 

where:

1.is the parameter coming from powerapps and should be like - Combobox.Selected.Value (from what i see the column name).

2. formula is (as you already figured out): length(body('Get_items')['value'] 

3. formula is similar to point 2 but refering at action Get_items_2.

 

Also in OnChange property of comboBox from PowerApps use a global / context variable to keep the response from flow like:

UpdateContext({answerFromFlow:Name_of_Flow(ComboBox.Selected.Value)})

and the text from the label under Efficient/Trained/Not Trained will be:

answerFromFlow.countEfficient ,  answerFromFlow.countTrained and answerFromFlow.countNotTrained.

 

May be it's not the brightest idea, but it will give you the expected results.  

 

Hope it will be usefull ...

@gabibalaban 

 

Thanks for the update. I tried to implement the flow but unfortunately I think i missed out something.


length(body('Get_items')?['value'])
length(body('Get_items2')?['value'])
length(body('Get_items3')?['value'])

akbarmahfuzalam_0-1614158255396.png

 

 

@akbarmahfuzalam ,

1. May be you missed an underline ? 

    length(body('Get_items_2')?['value'])
     length(body('Get_items_3')?['value'])
 
2. Use the same parameter asked from powerapps (not a new one). It seems that you  use 3 parameters from powerapps.  
3. The respond to powerapps action seems to be after one of get items action (in this way the doesn't know about the results of the other 2 get action items), not after you closed the parallel branch  as in my example.

@gabibalaban 

 

 

The template validation failed: 'The inputs of template action 'Respond_to_a_PowerApp_or_flow' at line '1 and column '4291' cannot reference action 'Get_items'. Action 'Get_items' must either be in 'runAfter' path or within a scope action on the 'runAfter' path of action 'Respond_to_a_PowerApp_or_flow', or be a Trigger.'.

akbarmahfuzalam_0-1614161169793.png

 

 

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Top Solution Authors
Top Kudoed Authors
Users online (59,085)