topic Re: Generate a table that runs calculations in one column based on values in another in Building Power Apps
https://powerusers.microsoft.com/t5/Building-Power-Apps/Generate-a-table-that-runs-calculations-in-one-column-based-on/m-p/593565#M186300
<P>Hi,</P><P> </P><P>You should be able to use the Countrows along with filter...</P><P> </P><P>If you want I can setup a teams session and we can go over it live Jay@research-nest.com</P>Thu, 11 Jun 2020 23:21:22 GMTJayMagness2020-06-11T23:21:22ZGenerate a table that runs calculations in one column based on values in another
https://powerusers.microsoft.com/t5/Building-Power-Apps/Generate-a-table-that-runs-calculations-in-one-column-based-on/m-p/593288#M186188
<P>I'm at a loss for how to communicate this in proper syntax, here's my best description of my needs.</P><P> </P><P>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.</P><P> </P><P>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.</P><P> </P><P>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.</P><P> </P><P>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.</P><P> </P><P>Also, in my use case, I'll never need to count more than the 500 record delegation limit.</P><P> </P>Thu, 11 Jun 2020 18:26:00 GMThttps://powerusers.microsoft.com/t5/Building-Power-Apps/Generate-a-table-that-runs-calculations-in-one-column-based-on/m-p/593288#M186188jrtpts2020-06-11T18:26:00ZRe: Generate a table that runs calculations in one column based on values in another
https://powerusers.microsoft.com/t5/Building-Power-Apps/Generate-a-table-that-runs-calculations-in-one-column-based-on/m-p/593565#M186300
<P>Hi,</P><P> </P><P>You should be able to use the Countrows along with filter...</P><P> </P><P>If you want I can setup a teams session and we can go over it live Jay@research-nest.com</P>Thu, 11 Jun 2020 23:21:22 GMThttps://powerusers.microsoft.com/t5/Building-Power-Apps/Generate-a-table-that-runs-calculations-in-one-column-based-on/m-p/593565#M186300JayMagness2020-06-11T23:21:22ZRe: Generate a table that runs calculations in one column based on values in another
https://powerusers.microsoft.com/t5/Building-Power-Apps/Generate-a-table-that-runs-calculations-in-one-column-based-on/m-p/593570#M186302
<P>I'm not sure the code in CDS but I'd assume it's something like this</P><P> </P><P>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.</P><P> </P><P>In PowerApps, you'd filter to today's date and then do a group by + sum to get the totals. </P><P> </P>Thu, 11 Jun 2020 23:39:10 GMThttps://powerusers.microsoft.com/t5/Building-Power-Apps/Generate-a-table-that-runs-calculations-in-one-column-based-on/m-p/593570#M186302dyee46142020-06-11T23:39:10ZRe: Generate a table that runs calculations in one column based on values in another
https://powerusers.microsoft.com/t5/Building-Power-Apps/Generate-a-table-that-runs-calculations-in-one-column-based-on/m-p/594896#M186742
<P>Thanks for your response!</P><P> </P><P>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.</P>Sat, 13 Jun 2020 14:12:01 GMThttps://powerusers.microsoft.com/t5/Building-Power-Apps/Generate-a-table-that-runs-calculations-in-one-column-based-on/m-p/594896#M186742jrtpts2020-06-13T14:12:01ZRe: Generate a table that runs calculations in one column based on values in another
https://powerusers.microsoft.com/t5/Building-Power-Apps/Generate-a-table-that-runs-calculations-in-one-column-based-on/m-p/595038#M186790
<P>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)</P>Sat, 13 Jun 2020 22:44:21 GMThttps://powerusers.microsoft.com/t5/Building-Power-Apps/Generate-a-table-that-runs-calculations-in-one-column-based-on/m-p/595038#M186790dyee46132020-06-13T22:44:21Z