Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all
I'm trying to find the missing numbers in the Meter Ticket No column for the data below
Each Truck has a unique sequence eg. EJ19 ABC starts at 15 and ends at 37, EJ19 DEF starts at 219 and ends at 236 and EJ19 HIJ starts at 263 and ends at 289.
Meter Ticket No | Truck | Delivery Date |
15 | EJ19 ABC | 22-Nov-22 |
16 | EJ19 ABC | 22-Nov-22 |
17 | EJ19 ABC | 22-Nov-22 |
18 | EJ19 ABC | 22-Nov-22 |
19 | EJ19 ABC | 22-Nov-22 |
22 | EJ19 ABC | 22-Nov-22 |
23 | EJ19 ABC | 22-Nov-22 |
26 | EJ19 ABC | 23-Nov-22 |
27 | EJ19 ABC | 23-Nov-22 |
28 | EJ19 ABC | 23-Nov-22 |
29 | EJ19 ABC | 23-Nov-22 |
30 | EJ19 ABC | 23-Nov-22 |
33 | EJ19 ABC | 24-Nov-22 |
34 | EJ19 ABC | 24-Nov-22 |
35 | EJ19 ABC | 24-Nov-22 |
36 | EJ19 ABC | 24-Nov-22 |
37 | EJ19 ABC | 24-Nov-22 |
38 | EJ19 ABC | 24-Nov-22 |
39 | EJ19 ABC | 24-Nov-22 |
219 | EJ19 EFG | 22-Nov-22 |
220 | EJ19 EFG | 22-Nov-22 |
221 | EJ19 EFG | 22-Nov-22 |
222 | EJ19 EFG | 22-Nov-22 |
223 | EJ19 EFG | 22-Nov-22 |
224 | EJ19 EFG | 22-Nov-22 |
225 | EJ19 EFG | 22-Nov-22 |
226 | EJ19 EFG | 22-Nov-22 |
227 | EJ19 EFG | 23-Nov-22 |
228 | EJ19 EFG | 23-Nov-22 |
229 | EJ19 EFG | 23-Nov-22 |
230 | EJ19 EFG | 23-Nov-22 |
231 | EJ19 EFG | 23-Nov-22 |
232 | EJ19 EFG | 23-Nov-22 |
234 | EJ19 EFG | 24-Nov-22 |
235 | EJ19 EFG | 24-Nov-22 |
236 | EJ19 EFG | 24-Nov-22 |
263 | EJ19 HIJ | 22-Nov-22 |
264 | EJ19 HIJ | 22-Nov-22 |
265 | EJ19 HIJ | 22-Nov-22 |
266 | EJ19 HIJ | 22-Nov-22 |
267 | EJ19 HIJ | 22-Nov-22 |
268 | EJ19 HIJ | 22-Nov-22 |
269 | EJ19 HIJ | 22-Nov-22 |
270 | EJ19 HIJ | 22-Nov-22 |
271 | EJ19 HIJ | 22-Nov-22 |
272 | EJ19 HIJ | 22-Nov-22 |
273 | EJ19 HIJ | 23-Nov-22 |
274 | EJ19 HIJ | 23-Nov-22 |
275 | EJ19 HIJ | 23-Nov-22 |
276 | EJ19 HIJ | 23-Nov-22 |
277 | EJ19 HIJ | 23-Nov-22 |
278 | EJ19 HIJ | 23-Nov-22 |
279 | EJ19 HIJ | 23-Nov-22 |
280 | EJ19 HIJ | 24-Nov-22 |
281 | EJ19 HIJ | 24-Nov-22 |
282 | EJ19 HIJ | 24-Nov-22 |
283 | EJ19 HIJ | 24-Nov-22 |
284 | EJ19 HIJ | 24-Nov-22 |
285 | EJ19 HIJ | 24-Nov-22 |
286 | EJ19 HIJ | 24-Nov-22 |
287 | EJ19 HIJ | 24-Nov-22 |
288 | EJ19 HIJ | 24-Nov-22 |
289 | EJ19 HIJ | 24-Nov-22 |
I need a way to identify when there are missing numbers in the sequences. For example EJ19 ABC is missing 20, 21, 24, 25, 31 and 32 across this date range.
EJ19 EFG is missing 233 and EJ19 HIJ is not missing any.
I looked into using GENERATESERIES but couldn't get it to work using DAX or M Query.
Anyone got any ideas please?
Hi @DaGemsta ,
Please try below steps:
1. create a measure with below dax formula
Measure =
VAR cur_truck =
SELECTEDVALUE ( 'Table'[Truck] )
VAR tmp =
FILTER ( ALL ( 'Table' ), 'Table'[Truck] = cur_truck )
VAR tmp1 =
CALCULATETABLE ( VALUES ( 'Table'[Meter Ticket No] ), tmp )
VAR max_no =
MAXX ( tmp, [Meter Ticket No] )
VAR min_no =
MINX ( tmp, [Meter Ticket No] )
VAR tmp2 =
GENERATESERIES ( COALESCE ( min_no, 0 ), COALESCE ( max_no, 0 ), 1 )
VAR tmp3 =
EXCEPT ( tmp2, tmp1 )
RETURN
CONCATENATEX ( tmp3, [Value], "," )
2. add a table visual with field and measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v 🙂
This measure works really well thank you so much 🙂
I've using a date slicer on my page for Delivery Date but this measure is not affected by it.
Is there any way to tweak it so that it is please?
Hi @DaGemsta ,
I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting and desensitized example data.
Thanks for your efforts & time in advance.
Best regards,
Community Support Team_Binbin Yu
Hi @HotChilli
Sorry I didn't explain that very well.
Here's an example - EJ19 ABC had a firmware upgrade on the 25th Nov and that reset the ticket count back to 1.
Meter Ticket No | Truck | Delivery Date |
415 | EJ19 ABC | 22-Nov-22 |
416 | EJ19 ABC | 22-Nov-22 |
417 | EJ19 ABC | 22-Nov-22 |
418 | EJ19 ABC | 22-Nov-22 |
419 | EJ19 ABC | 22-Nov-22 |
422 | EJ19 ABC | 22-Nov-22 |
423 | EJ19 ABC | 22-Nov-22 |
426 | EJ19 ABC | 23-Nov-22 |
427 | EJ19 ABC | 23-Nov-22 |
428 | EJ19 ABC | 23-Nov-22 |
429 | EJ19 ABC | 23-Nov-22 |
430 | EJ19 ABC | 23-Nov-22 |
433 | EJ19 ABC | 24-Nov-22 |
434 | EJ19 ABC | 24-Nov-22 |
435 | EJ19 ABC | 24-Nov-22 |
436 | EJ19 ABC | 24-Nov-22 |
437 | EJ19 ABC | 24-Nov-22 |
438 | EJ19 ABC | 24-Nov-22 |
439 | EJ19 ABC | 24-Nov-22 |
1 | EJ19 ABC | 25-Nov-22 |
2 | EJ19 ABC | 25-Nov-22 |
3 | EJ19 ABC | 25-Nov-22 |
4 | EJ19 ABC | 25-Nov-22 |
6 | EJ19 ABC | 25-Nov-22 |
10 | EJ19 ABC | 25-Nov-22 |
11 | EJ19 ABC | 25-Nov-22 |
12 | EJ19 ABC | 25-Nov-22 |
13 | EJ19 ABC | 25-Nov-22 |
15 | EJ19 ABC | 26-Nov-22 |
22 | EJ19 ABC | 26-Nov-22 |
23 | EJ19 ABC | 26-Nov-22 |
24 | EJ19 ABC | 26-Nov-22 |
25 | EJ19 ABC | 26-Nov-22 |
26 | EJ19 ABC | 26-Nov-22 |
27 | EJ19 ABC | 26-Nov-22 |
30 | EJ19 ABC | 26-Nov-22 |
31 | EJ19 ABC | 26-Nov-22 |
32 | EJ19 ABC | 26-Nov-22 |
33 | EJ19 ABC | 26-Nov-22 |
35 | EJ19 ABC | 27-Nov-22 |
37 | EJ19 ABC | 27-Nov-22 |
38 | EJ19 ABC | 27-Nov-22 |
39 | EJ19 ABC | 27-Nov-22 |
40 | EJ19 ABC | 27-Nov-22 |
41 | EJ19 ABC | 27-Nov-22 |
42 | EJ19 ABC | 28-Nov-22 |
43 | EJ19 ABC | 28-Nov-22 |
44 | EJ19 ABC | 28-Nov-22 |
45 | EJ19 ABC | 28-Nov-22 |
48 | EJ19 ABC | 28-Nov-22 |
49 | EJ19 ABC | 28-Nov-22 |
50 | EJ19 ABC | 28-Nov-22 |
51 | EJ19 ABC | 29-Nov-22 |
52 | EJ19 ABC | 29-Nov-22 |
55 | EJ19 ABC | 29-Nov-22 |
56 | EJ19 ABC | 29-Nov-22 |
57 | EJ19 ABC | 29-Nov-22 |
58 | EJ19 ABC | 29-Nov-22 |
59 | EJ19 ABC | 29-Nov-22 |
60 | EJ19 ABC | 30-Nov-22 |
61 | EJ19 ABC | 30-Nov-22 |
62 | EJ19 ABC | 30-Nov-22 |
63 | EJ19 ABC | 30-Nov-22 |
So this truck now has two sequences, which means 2 MIN's and 2 MAX's.
First sequence will have MIN = 415 and MAX = 439
Second sequence will have MIN = 1 and MAX = 63
With the current method, the MIN for this truck shows as 1 and MAX shows as 439, which is giving the wrong amount of missing numbers.
Is there a way, by using the date maybe, to identify how may sequences a truck has and record the 2 x MIN's and 2 x MAX's? So you could have MINA = 415 MAXA = 439 and MINB = 1 MAXB = 63 and then work out the missing tickets from the two?
I hope that makes more sense?
Hi @DaGemsta ,
You could try to create a firmware upgrade date table for each truck, then according the date table to calculate.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
do you need it?
VAR _Tbl1 =
GENERATEALL (
VALUES ( tbl[Truck] ),
GENERATESERIES (
CALCULATE ( MIN ( 'tbl'[Meter Ticket No] ) ),
CALCULATE ( MAX ( 'tbl'[Meter Ticket No] ) )
)
)
VAR _tbl2 =
SELECTCOLUMNS ( 'tbl', "Truck", 'tbl'[Truck], "Vulue", 'tbl'[Meter Ticket No] )
VAR _Tbl3 =
EXCEPT ( _Tbl1, _tbl2 )
RETURN
_Tbl3
Please check attached pbix for more details.
If you want a Power Query solution you can
1. duplicate the table
2. 'Group By' the Truck with 2 aggregations (Minimum and Maximum ticket number). Call them MinTick, MaxTick
3. Add a custom column like this {[MinTick]..[MaxTick]}
4. This will create a list in a column which you can expand from the header
5. You now have a table with the Truck and rows for every Ticket Number that applies.
Use this table in a merge with the original table (on Truck and Ticket Number) using an Anti-join. This shows rows from one table which are not in the other table.
Have a go at that and get back to me if you can't get it to work
Hi Hotty! 😁
I've got this working but have an issue with my source data and wondered if you can think of a way around it?
Occasionally the meters have a software upgrade and the Meter Ticket No goes back to 1 and starts counting again...
Would there be a way to compensate for this? Maybe link the sequence to the date order so it will see that some of the trucks have two sequences?
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |