Forum Discussion

akslagzim's avatar
akslagzim
Frequent Visitor
1 year ago
Solved

Creating a column verifying whether the numbers create a numerical sequence

Hello,

 

I have a data which are divided by group (A, B, C). Number of rows for each group can equals from 1 to 8. Every row is defined by column "Number". I need to create a column that will be verifying if the numbers in each group are one by one.

 

Example,

In group A every number is one by one - OK.

In group B number 20240506 is not after 20240504 (20240505 is missing) so for all rows in group B the column will return the result "NOK".

The same situation in group C.

 

GroupNumberResult
A20240801OK
A20240802OK
A20240803OK
B20240503NOK
B20240504NOK
B20240506NOK
C20231000NOK
C20231001NOK
C20231010NOK
C20231011NOK

 

I was thinking to define MIN number in each group and after that verify if in column appears number +1, +2, +3 etc. but I have problem to wirite this... and maybe there is another way to do this.

 

Any guidance you can provide would be invaluable.

Thank you in advance 🙂

  • johnt75's avatar
    johnt75
    1 year ago

    Try

    Result =
VAR _Min =
    CALCULATE ( MIN ( 'Table'[Number] ), ALLEXCEPT ( 'Table', 'Table'[Group] ) )
VAR _Max =
    CALCULATE ( MAX ( 'Table'[Number] ), ALLEXCEPT ( 'Table', 'Table'[Group] ) )
VAR _NumRows =
    CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Group] ) )
VAR Result =
    IF ( _Max - _Min = _NumRows + 1, "OK", "NOK" )
RETURN
    Result

2 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    Try

    Result =
VAR _Min =
    CALCULATE ( MIN ( 'Table'[Number] ), ALLEXCEPT ( 'Table', 'Table'[Group] ) )
VAR _Max =
    CALCULATE ( MAX ( 'Table'[Number] ), ALLEXCEPT ( 'Table', 'Table'[Group] ) )
VAR _NumRows =
    CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Group] ) )
VAR Result =
    IF ( _Max - _Min = _NumRows + 1, "OK", "NOK" )
RETURN
    Result