Hi All,
I'm currently trying to count rows between a certain date and only once per person from a SharePoint list.
I have it working for counting between the dates :
CountRows((Filter(WorkFromHome,Value(Text('DateUsed',"[$-en-US]yyyymmdd"))>=Value(Text(DateAdd(Today(),-30),"[$-en-US]yyyy-mm-dd")))))
I also have one working with only rows with a unique name:
CountRows(Distinct(WorkFromHome.Name,Name))
I know I need to combine the two somehow but I'm Stuck after a few hours.
Any help at all would be much appreciated !
Thanks!
My idea is to 1st collect the Distinct Names and then make a 2nd collection where we add a column to show the count.
ClearCollect(
myUniqueNames,
Distinct(WorkFromHome.Name,Name) // produces a single column called Result
);
ClearCollect(
myCountByName,
AddColumns(
myUniqueNames,
"myCount",
CountRows(
Filter(
WorkFromHome,
Name = myUniqueNames[@Result],
Value(Text('DateUsed',"[$-en-US]yyyymmdd"))>=Value(Text(DateAdd(Today(),-30),"[$-en-US]yyyy-mm-dd"))
)
)
);
This was the key piece of code added to the 2nd collection
Name = myUniqueNames[@Result]
---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."
Hi @mdevaney ,
Thank you for your help!
I'm Currently getting this error when I attempt to use your solution:
Do you know how I can resolve this?
Thank you again for your help!
Its just a syntax error. Please change this line...
\\ produces a single column called Result
...into this code.
// produces a single column called Result
---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."
Still not working, it seems like there's an issue with the name of the collection, do i need to put it into the "OnStart" of the app maybe?
Suggest you try to get this small part working first. Then add the 2nd part.
ClearCollect(
myUniqueNames,
Distinct(WorkFromHome.Name,Name)
);
(Also, I can't see part of the text on your screen because the error message is in the way, lol)
I have a similar problem trying to get record counts from a series of normalized database tables. Your solution worked great for my problem, really appreciate your explanation. I was wondering, do you know what the difference is between:
myUniqueNames[@Result]
and
myUniqueNames.Result
I've noticed that the former produces the serialized counts that I need, while the latter just counts all records in the normalized table. I found this:
https://powerusers.microsoft.com/t5/Building-Power-Apps/How-does-the-quot-quot-notation-work/td-p/72...
...but it suggests [@*] syntax is used to differentiate between columns with identical names or columns with identical names as other elements.
User | Count |
---|---|
198 | |
124 | |
88 | |
48 | |
41 |
User | Count |
---|---|
280 | |
166 | |
138 | |
82 | |
76 |