cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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.

 

4 REPLIES 4
Highlighted
Resolver V
Resolver V

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

Highlighted
Helper V
Helper V

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.  

 

Highlighted

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.

Highlighted

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)

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (11,030)