Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Compare value with all previous rows

Hi!

I started working with Power BI and I have a task that I know that needs a calculated column, however I'm having some difficulties with it.

I need to compare the current cell of a column Sequence with all previous rows and write on the new calculated column "Ok" if it is the highest number so far, or "NOK" otherwise, as the example below.

 

Seq.ok/nok
3280ok
3281ok
3282ok
3284ok
3283nok
3285ok
3286ok
3287ok
3288ok
3289ok
3268nok
3274nok
3290ok
3291ok
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi Anonymous ,

     

    At first, create an index column in the query editor.

    Then you could create a column or a measure to get the result.

    Column:

    Column =
VAR a = 'Table'[Index]
VAR b =
    CALCULATE (
        MAX ( 'Table'[Seq.] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Index] <= a )
    )
RETURN
    IF ( b = 'Table'[Seq.], "OK", "NOK" )

    Measure:

    Measure =
VAR a =
    SELECTEDVALUE ( 'Table'[Index] )
VAR b =
    CALCULATE (
        MAX ( 'Table'[Seq.] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Index] <= a )
    )
RETURN
    IF ( b = SELECTEDVALUE ( 'Table'[Seq.] ), "OK", "NOK" )

    Here is the result.

    Here is my test file for your reference.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous ,

     

     

    Create an index Column from Power Query.

     

     

     

    Then Create a Calculated Column

     

    OKNOOK = 
var a = CALCULATE(MIN('Table'[Seq.]), FILTER('Table','Table'[Index] = EARLIER('Table'[Index])-1))

RETURN
IF('Table'[Seq.] > a, "OK", "NOOK")

     

     

     

     


    Regards,

    Harsh Nathani


    Appreciate with a Kudos!! (Click the Thumbs Up Button)

    Did I answer your question? Mark my post as a solution!

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    At first, create an index column in the query editor.

    Then you could create a column or a measure to get the result.

    Column:

    Column =
VAR a = 'Table'[Index]
VAR b =
    CALCULATE (
        MAX ( 'Table'[Seq.] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Index] <= a )
    )
RETURN
    IF ( b = 'Table'[Seq.], "OK", "NOK" )

    Measure:

    Measure =
VAR a =
    SELECTEDVALUE ( 'Table'[Index] )
VAR b =
    CALCULATE (
        MAX ( 'Table'[Seq.] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Index] <= a )
    )
RETURN
    IF ( b = SELECTEDVALUE ( 'Table'[Seq.] ), "OK", "NOK" )

    Here is the result.

    Here is my test file for your reference.