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.
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.
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.
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)
Check out the on demand sessions that are available now!
Stay up tp date on the latest blogs and activities in the community News & Announcements.
Features releasing from October 2020 through March 2021
Check out the Power Platform Community Highlights