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

CountIf to add to a distinct collection

Hi community, 

 

I am trying to create a single-column collection with a distinct from a certain Filter(). To this collection (and that's the issue), I want to add the numbers (hitcounts) of this distinct result. I have tried mutliple approaches, but not successful. Any help appreciated. Those were not successful:

 

AddColumns(xxx, "blah blah", ForAll(xxx.Result,CountIf(wasd, 'Affected Quarter'.Value = ThisRecord.'Affected Quarter'.Value)))

 

ForAll(xxx.Result, If(xxx.Result in wasd.'Affected Quarter', AddColumns(wasd, "blah blah", ThisRecord.'Affected Quarter'.Value)))

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @strahlemann42 ,

I just did a further test on this end using a similar one-column (complex) field and it worked fine - you do however need to use the actual original SharePoint Name

AddColumns(
   GroupBy(
      AddColumns(  
         wasd, 
         "Quarter",
         Affected_x0020_Quarters.Value
      ),
      "Quarter",
      "OtherData"
   ),
   "Totals",
   CountRows(OtherData)
)

You will have two columns - Quarters and Totals.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

5 REPLIES 5
WarrenBelz
Super User III
Super User III

Hi @strahlemann42 ,

Try this

AddColumns(
   GroupBy(
      AddColumns(  
         wasd, 
         "Quarter",
         'Affected Quarter'.Value
      ),
      "Quarter",
      "OtherData"
   ),
   "Totals",
   CountRows(OtherData)
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

PowerRanger
Resolver II
Resolver II

Hi,

 

If i understand this right you might want to look into the GroupBy Function

 

GroupBy and Ungroup functions - Power Apps | Microsoft Docs

 

 

Yes @PowerRanger .

That is exactly the way I approached it.

Hi @WarrenBelz, thanks for pushing me to the group function. I now see that given the column name in SPO, I might have an issue working with the column or the collection entirely. Let me elaborate a bit further as I see the issue needs more explanation: 

 

I start with this to build a collection: 

ClearCollect(wasd, Filter([@TABLE], ISOCOUNTRYCODE = "GR").'Affected Quarter')

As you can see, the column name I am interested in, holds a space character, but the collection "wasd" works with the complex data: 

strahlemann42_1-1603348600613.png

 

strahlemann42_0-1603348588768.png

Now I want to manipulate the collection by adding a column to extract the value from the first one (values can be "FQ1/2017" for example. Later, I could do the grouping as per your suggestion on the non-complex data... Trying this, I recognize the column name is not the same as the original one: 

Screenshot 2020-10-22 081952.png

Now, neither data manipulation, nor adding a simple column works with the collection... anytime I check it remains the same. 

AddColumns(wasd, "test", ThisRecord.'Affected Quarter'.Value)

This did not work, nor does a simple adding an empty string:

AddColumns(wasd, "test", "xxx")

 

Thanks again 😕

Hi @strahlemann42 ,

I just did a further test on this end using a similar one-column (complex) field and it worked fine - you do however need to use the actual original SharePoint Name

AddColumns(
   GroupBy(
      AddColumns(  
         wasd, 
         "Quarter",
         Affected_x0020_Quarters.Value
      ),
      "Quarter",
      "OtherData"
   ),
   "Totals",
   CountRows(OtherData)
)

You will have two columns - Quarters and Totals.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,482)