CountRows If using Distinct on a different column

[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!

[mdevaney]

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

[DarylMcC]

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!

[mdevaney]

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

[DarylMcC]

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

[mdevaney]

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

[orbis_admin]

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

[mdevaney]

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