Skip to main content

Notifications

Power Apps - Building Power Apps
Unanswered

Generate a table that runs calculations in one column based on values in another

(0) ShareShare
ReportReport
Posted on by 19

I'm at a loss for how to communicate this in proper syntax, here's my best description of my needs.

 

I am working with a CDS entity for my data source. For the scope of this question, say that each record contains a date, four yes/no fields, and an option-set field, which contains 20 options.

 

I need to create a tool which will filter the records by a date range (via date picker controls), then generate a table which - for each option set value - counts how many records have "yes" values in all yes/no fields.

 

So my table should end up with twenty rows. One column will list out those twenty option set values, then another column will give the number of records that have that option chosen, have "yes" in the yes/no fields, and which are within the date range set by the date controls.

 

I have attempted several different configurations of collection(s), and different assortments of ShowColumns, AddColumns, GroupBy, Distinct and LookUps, and have hit some wall or other every time. This feels like a straightforward manipulation of my data, but I can't find any documentation that says how to do this.

 

Also, in my use case, I'll never need to count more than the 500 record delegation limit.

 

Categories:
  • dyee4613 Profile Picture
    dyee4613 301 on at
    Re: Generate a table that runs calculations in one column based on values in another

    You want something like sum(datasource.field).  To do like a count if it would be something like Counta(Filter(DataSource, filter field = filter.text).countfield)

  • jrtpts Profile Picture
    jrtpts 19 on at
    Re: Generate a table that runs calculations in one column based on values in another

    Thanks for your response!

     

    I understand what the formula would look like to assess a single set of values, I just don't know how to place the option set values as a column in a table, or how to have that countif/filter function run for each of those values.

  • dyee4614 Profile Picture
    dyee4614 283 on at
    Re: Generate a table that runs calculations in one column based on values in another

    I'm not sure the code in CDS but I'd assume it's something like this

     

    IF(Column1 = "Yes", 1, 0 )+IF(Column2 = "Yes", 1, 0 )+IF(Column3 = "Yes", 1, 0 )+IF(Column4 = "Yes", 1, 0 ).  This will give you the total per row.

     

    In PowerApps, you'd filter to today's date and then do a group by + sum to get the totals.  

     

  • JayMagness Profile Picture
    JayMagness 1,663 on at
    Re: Generate a table that runs calculations in one column based on values in another

    Hi,

     

    You should be able to use the Countrows along with filter...

     

    If you want I can setup a teams session and we can go over it live Jay@research-nest.com

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,636

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,942

Leaderboard