topic Dax to group by and count with Filter in DAX Commands and Tips

Dax to group by and count with Filter

dhanekula1996 — Thu, 01 Sep 2022 15:04:06 GMT

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

Re: Dax to group by and count with Filter

Adescrit — Sat, 03 Sep 2022 11:44:10 GMT

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.

Re: Dax to group by and count with Filter

Whitewater100 — Sat, 03 Sep 2022 14:02:32 GMT

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!

Re: Dax to group by and count with Filter

Anonymous — Tue, 06 Sep 2022 06:14:11 GMT

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.