Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DaGemsta
Helper I
Helper I

Find the missing numbers in the sequency across many dates and for many different sequences

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 NoTruckDelivery Date
15EJ19 ABC22-Nov-22
16EJ19 ABC22-Nov-22
17EJ19 ABC22-Nov-22
18EJ19 ABC22-Nov-22
19EJ19 ABC22-Nov-22
22EJ19 ABC22-Nov-22
23EJ19 ABC22-Nov-22
26EJ19 ABC23-Nov-22
27EJ19 ABC23-Nov-22
28EJ19 ABC23-Nov-22
29EJ19 ABC23-Nov-22
30EJ19 ABC23-Nov-22
33EJ19 ABC24-Nov-22
34EJ19 ABC24-Nov-22
35EJ19 ABC24-Nov-22
36EJ19 ABC24-Nov-22
37EJ19 ABC24-Nov-22
38EJ19 ABC24-Nov-22
39EJ19 ABC24-Nov-22
219EJ19 EFG22-Nov-22
220EJ19 EFG22-Nov-22
221EJ19 EFG22-Nov-22
222EJ19 EFG22-Nov-22
223EJ19 EFG22-Nov-22
224EJ19 EFG22-Nov-22
225EJ19 EFG22-Nov-22
226EJ19 EFG22-Nov-22
227EJ19 EFG23-Nov-22
228EJ19 EFG23-Nov-22
229EJ19 EFG23-Nov-22
230EJ19 EFG23-Nov-22
231EJ19 EFG23-Nov-22
232EJ19 EFG23-Nov-22
234EJ19 EFG24-Nov-22
235EJ19 EFG24-Nov-22
236EJ19 EFG24-Nov-22
263EJ19 HIJ22-Nov-22
264EJ19 HIJ22-Nov-22
265EJ19 HIJ22-Nov-22
266EJ19 HIJ22-Nov-22
267EJ19 HIJ22-Nov-22
268EJ19 HIJ22-Nov-22
269EJ19 HIJ22-Nov-22
270EJ19 HIJ22-Nov-22
271EJ19 HIJ22-Nov-22
272EJ19 HIJ22-Nov-22
273EJ19 HIJ23-Nov-22
274EJ19 HIJ23-Nov-22
275EJ19 HIJ23-Nov-22
276EJ19 HIJ23-Nov-22
277EJ19 HIJ23-Nov-22
278EJ19 HIJ23-Nov-22
279EJ19 HIJ23-Nov-22
280EJ19 HIJ24-Nov-22
281EJ19 HIJ24-Nov-22
282EJ19 HIJ24-Nov-22
283EJ19 HIJ24-Nov-22
284EJ19 HIJ24-Nov-22
285EJ19 HIJ24-Nov-22
286EJ19 HIJ24-Nov-22
287EJ19 HIJ24-Nov-22
288EJ19 HIJ24-Nov-22
289EJ19 HIJ24-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?

8 REPLIES 8
v-binbinyu-msft
Community Support
Community Support

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 

vbinbinyumsft_0-1673854525662.png

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 NoTruckDelivery Date
415EJ19 ABC22-Nov-22
416EJ19 ABC22-Nov-22
417EJ19 ABC22-Nov-22
418EJ19 ABC22-Nov-22
419EJ19 ABC22-Nov-22
422EJ19 ABC22-Nov-22
423EJ19 ABC22-Nov-22
426EJ19 ABC23-Nov-22
427EJ19 ABC23-Nov-22
428EJ19 ABC23-Nov-22
429EJ19 ABC23-Nov-22
430EJ19 ABC23-Nov-22
433EJ19 ABC24-Nov-22
434EJ19 ABC24-Nov-22
435EJ19 ABC24-Nov-22
436EJ19 ABC24-Nov-22
437EJ19 ABC24-Nov-22
438EJ19 ABC24-Nov-22
439EJ19 ABC24-Nov-22
1EJ19 ABC25-Nov-22
2EJ19 ABC25-Nov-22
3EJ19 ABC25-Nov-22
4EJ19 ABC25-Nov-22
6EJ19 ABC25-Nov-22
10EJ19 ABC25-Nov-22
11EJ19 ABC25-Nov-22
12EJ19 ABC25-Nov-22
13EJ19 ABC25-Nov-22
15EJ19 ABC26-Nov-22
22EJ19 ABC26-Nov-22
23EJ19 ABC26-Nov-22
24EJ19 ABC26-Nov-22
25EJ19 ABC26-Nov-22
26EJ19 ABC26-Nov-22
27EJ19 ABC26-Nov-22
30EJ19 ABC26-Nov-22
31EJ19 ABC26-Nov-22
32EJ19 ABC26-Nov-22
33EJ19 ABC26-Nov-22
35EJ19 ABC27-Nov-22
37EJ19 ABC27-Nov-22
38EJ19 ABC27-Nov-22
39EJ19 ABC27-Nov-22
40EJ19 ABC27-Nov-22
41EJ19 ABC27-Nov-22
42EJ19 ABC28-Nov-22
43EJ19 ABC28-Nov-22
44EJ19 ABC28-Nov-22
45EJ19 ABC28-Nov-22
48EJ19 ABC28-Nov-22
49EJ19 ABC28-Nov-22
50EJ19 ABC28-Nov-22
51EJ19 ABC29-Nov-22
52EJ19 ABC29-Nov-22
55EJ19 ABC29-Nov-22
56EJ19 ABC29-Nov-22
57EJ19 ABC29-Nov-22
58EJ19 ABC29-Nov-22
59EJ19 ABC29-Nov-22
60EJ19 ABC30-Nov-22
61EJ19 ABC30-Nov-22
62EJ19 ABC30-Nov-22
63EJ19 ABC30-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.

Ahmedx
Super User
Super User

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

 

Screen Capture #217.pngScreen Capture #218.png

Please check attached pbix for more details.

https://dropmefiles.com/kRDX0

 

 

 

HotChilli
Super User
Super User

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.