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 ve...
  • 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
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