Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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:
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 |
Solved! Go to 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.
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.
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!
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.
User | Count |
---|---|
15 | |
10 | |
9 | |
9 | |
8 |