cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

CountIf and Count Distinct

I have a coffee roasting Application that tracks Batchs.  I wanted to add some functionality to show the total number of Roast for the Day (that one was easy, Count(Batch_Data, roastdateasint = Value(Text(roastDate.SelectedDate, "yyyymmdd")))   ). That gives me the total number of batches for the day.  

 

Next i want to show How many batches were completed.  Once a batch is complete the Green Bean loader submits the blend recipe to the Database.  This Patch can write 1 record or it can write 4 records, depending on the number of beans in the blend.  When it writes to the Green_Data Table it also uses the Batch_ID (ID from Batch_Data) as a foreign key.  I am having one H$%^ of a time trying to figure this out. I have tried CountIf with Distinct and many other ways.  

 

So Batch_Data is where I create the schedule for the days Batches and Green_Data is where the Green bean information is written to once the blend is created (somewhat like the table below).  

 

How do I count distinct Batch_ID but also filter that on DateAsInt?  The result for the below table if counting distinct on 20190925 would show 3.

 

ID           Batch_ID               Green_ID                  DateAsInt
1                 2                              1                          20190925
2                 2                              5                          20190925
3                 3                              1                          20190925
4                 4                              2                          20190925
5                 4                              3                          20190925
6                 5                              1                          20190926

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: CountIf and Count Distinct

@Anonymous 

Assuming your collection looks like this:

 

ClearCollect(
    BatchData,
    {ID: 1, Batch_ID: 2, Green_ID: 1, DateAsInt: 20190925},
    {ID: 2, Batch_ID: 2, Green_ID: 5, DateAsInt: 20190925},
    {ID: 3, Batch_ID: 3, Green_ID: 1, DateAsInt: 20190925},
    {ID: 4, Batch_ID: 4, Green_ID: 2, DateAsInt: 20190925},
    {ID: 5, Batch_ID: 4, Green_ID: 3, DateAsInt: 20190925},
    {ID: 6, Batch_ID: 5, Green_ID: 1, DateAsInt: 20190926}
)

 

I would suggest the following function to determine the count of distinct batch IDs brewed on 20190925

 

Count(Distinct(Filter(BatchData,DateAsInt=20190925),Batch_ID)))

 

The result would be

 

3

 

---
Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."

View solution in original post

3 REPLIES 3
Super User
Super User

Re: CountIf and Count Distinct

@Anonymous 

Assuming your collection looks like this:

 

ClearCollect(
    BatchData,
    {ID: 1, Batch_ID: 2, Green_ID: 1, DateAsInt: 20190925},
    {ID: 2, Batch_ID: 2, Green_ID: 5, DateAsInt: 20190925},
    {ID: 3, Batch_ID: 3, Green_ID: 1, DateAsInt: 20190925},
    {ID: 4, Batch_ID: 4, Green_ID: 2, DateAsInt: 20190925},
    {ID: 5, Batch_ID: 4, Green_ID: 3, DateAsInt: 20190925},
    {ID: 6, Batch_ID: 5, Green_ID: 1, DateAsInt: 20190926}
)

 

I would suggest the following function to determine the count of distinct batch IDs brewed on 20190925

 

Count(Distinct(Filter(BatchData,DateAsInt=20190925),Batch_ID)))

 

The result would be

 

3

 

---
Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."

View solution in original post

Highlighted
Community Support Team
Community Support Team

Re: CountIf and Count Distinct

Hi @Anonymous ,

Based on the needs that you mentioned, I think the Distinct function could achieve your needs. I have made a test on my side, please take a try with the following workaround:

CountRows(
      Distinct(
                  Filter(
                           Batch_Data, 
                           roastdateasint = Value(Text(roastDate.SelectedDate, "yyyymmdd"))
                  ),
                   Batch_ID
      )
)

In addition, I also think the Group function could achieve your needs. Please take a try with the following workaround:

CountRows(
GroupBy(
               Filter(
                           Batch_Data, 
                           roastdateasint = Value(Text(roastDate.SelectedDate, "yyyymmdd"))
                ),
                "Batch_ID",
               "GroupData"
)
)

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: CountIf and Count Distinct

Thank you, 

 

This worked once I changed the expression to 

Count(Distinct(Filter('[dbo].[Green_Data]',DateAsInt=Value(Text(roastDate.SelectedDate, "yyyymmdd")),Batch_ID)))


Thanks!!

 

 

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,584)