CountRows If using Distinct on a different column

(0) Share

Report

Posted on by DarylMcC

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!

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

Sort by

mdevaney 29,991 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
@DarylMcC

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."

Was this reply helpful? Yes No
DarylMcC 20 on at

Like
a
(0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@DarylMcC

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."

Was this reply helpful? Yes No
DarylMcC 20 on at

Like
a
(0)

Report
Copy link

Link copied!

@mdevaney
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?

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@DarylMcC

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)

Was this reply helpful? Yes No
orbis_admin 12 on at

Like
a
(0)

Report
Copy link

Link copied!

@mdevaney,

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/7265

...but it suggests [@*] syntax is used to differentiate between columns with identical names or columns with identical names as other elements.

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@orbis_admin
Start a new thread. This one is old: from last year.

Was this reply helpful? Yes No