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

CountRows If using Distinct on a different column

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!

 

7 REPLIES 7
Highlighted
Super User
Super User

Re: CountRows If using Distinct on a different column

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

Highlighted
Frequent Visitor

Re: CountRows If using Distinct on a different column

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!

 

 

image.png

Highlighted
Super User
Super User

Re: CountRows If using Distinct on a different column

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

Highlighted
Frequent Visitor

Re: CountRows If using Distinct on a different column

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

 

image.png

Highlighted
Super User
Super User

Re: CountRows If using Distinct on a different column

@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)

Highlighted
Frequent Visitor

Re: CountRows If using Distinct on a different column

@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/72...

 

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

Highlighted
Super User
Super User

Re: CountRows If using Distinct on a different column

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

Helpful resources

Announcements
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,604)