Dax to group by and count with Filter

[dhanekula1996]

Hello,
I am trying to count the distinct stop numbers which are "On time".  I need to group by driver name, date, and stop number and get the count of On-Time deliveries. 
I got the required Solution using this DAX: 

On_Time_Del = COUNTROWS(SUMMARIZE(FILTER('Samp_table,'Samp_table'[Type of Stop]="On Time Stop"),'Samp_table'[Date],'Samp_table'[stop_number]))

But this is extremely slow!! Is there any other way I can get the solution? 
Thank you for your time!!

This is a sample driver's data for example This driver made 14 stops in a day and on 13 the top he delivered 2 order.

Sample_Data:

Id	Name	Date	Stop Number	Order Number	Type of Stop
1	Mark Hudson	8/23/2022	1	123	On Time
2	Mark Hudson	8/23/2022	2	456	On Time
3	Mark Hudson	8/23/2022	3	789	On Time
4	Mark Hudson	8/23/2022	4	134	On Time
5	Mark Hudson	8/23/2022	5	156	On Time
6	Mark Hudson	8/23/2022	6	45546	On Time
7	Mark Hudson	8/23/2022	7	56566	Delayed
8	Mark Hudson	8/23/2022	8	6567	Delayed
9	Mark Hudson	8/23/2022	9	178	On Time
10	Mark Hudson	8/23/2022	10	46677	On Time
11	Mark Hudson	8/23/2022	11	465665	On Time
12	Mark Hudson	8/23/2022	12	3434	On Time
13	Mark Hudson	8/23/2022	13	232344	On Time
14	Mark Hudson	8/23/2022	13	25554	On Time
15	Mark Hudson	8/23/2022	14	87900	Delayed

@amitchandak 

[v-rzhou-msft · Accepted solution]

Hi @dhanekula1996 ,

I suggest you to try this code to create a measure.

On_Time_Del =
CALCULATE (
    COUNT ( Samp_table[Id] ),
    FILTER (
        ALLEXCEPT (
            Samp_table,
            Samp_table[Name],
            Samp_table[Date],
            Samp_table[Stop Number]
        ),
        'Samp_table'[Type of Stop] = "On Time Stop"
    )
)

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Whitewater100]

Hi: A faster way can be to go to Transform Data and use GroupBy:

Then your table can look like this:

It's not DAX but a way to solve. I hope this helps!

[Adescrit]

Hi @dhanekula1996 ,

How about this:

# On-Time = 
COUNTROWS (
    SUMMARIZECOLUMNS (
        'Table'[Name],
        'Table'[Date],
        'Table'[Stop Number],
        FILTER ( 'Table', 'Table'[Type of Stop] = "On Time" )
    )
)

When you want to filter a calculated/summarized table, SUMMARIZECOLUMNS is a nice solution.