Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

PrevRow + index problem

Guys, i'm using dax to get my prev row value based on my index number. 
First, let's have a look at my table

Ok, i need to sort first by my ID number to group all values that are the same, then i need to sort by date/time, to get the order of how our process was done. 

Then i created my index to get my prev value using a var. 

The problem is, when my BI are updated, a new item enter as my last index and doesnt group with the other id numbers. 
And this new item will not be counted in my process because it dont fill the requirementes of my if. 

This is how i got my prev row "

 

Acionamento =
var idx = [Index]
var id_num = [ID Number]
return
CALCULATE(MAX([Changed On Timestamp]),FILTER(table, 'table'[Index] = idx-1 )))"

So, i have no idea how to fix it. 
General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hello @Vieiraguilherme ,

    You can create this computed column instead of the index column:

    Sort =
VAR _rank =
    RANKX (
        FILTER ( ALL ( 'Table' ), 'Table'[ID Number] = EARLIER ( 'Table'[ID Number] ) ),
        'Table'[Changed On Timestamp],
        ,
        ASC,
        DENSE
    )
VAR _sort =
    IF (
        [ID Number] = MIN ( 'Table'[ID Number] ),
        _rank,
        _rank
            + CALCULATE (
                COUNTROWS (
                    FILTER ( ALL ( 'Table' ), 'Table'[ID Number] < MAX ( 'Table'[ID Number] ) )
                )
            )
    )
RETURN
    _sort

    Next, create a measure to get the previous row:

    _prerow = 
CALCULATE (
    MAX ( 'Table'[Changed On Timestamp] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Sort]
            = CALCULATE (
                MAX ( 'Table'[Sort] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Sort] < MAX ( 'Table'[Sort] ) )
            )
    )
)

    Current Result:

    before.png

    When you add a new row to the table, the value of the previous row is still received successfully:

    after.png

    Attached a sample file in the next one, hopes to help you.

    Best Looks,
    Yingjie Li

    If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try like

    new measure

    Acionamento =
    CALCULATE(MAX([Changed On Timestamp]),FILTER(table, 'table'[Index] = max( 'table'[Index] )-1))

     

    new column

    Acionamento =
    CALCULATE(MAX([Changed On Timestamp]),FILTER(table, 'table'[Index] = earlier( 'table'[Index] )-1))

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    Hello @Vieiraguilherme ,

    You can create this computed column instead of the index column:

    Sort =
VAR _rank =
    RANKX (
        FILTER ( ALL ( 'Table' ), 'Table'[ID Number] = EARLIER ( 'Table'[ID Number] ) ),
        'Table'[Changed On Timestamp],
        ,
        ASC,
        DENSE
    )
VAR _sort =
    IF (
        [ID Number] = MIN ( 'Table'[ID Number] ),
        _rank,
        _rank
            + CALCULATE (
                COUNTROWS (
                    FILTER ( ALL ( 'Table' ), 'Table'[ID Number] < MAX ( 'Table'[ID Number] ) )
                )
            )
    )
RETURN
    _sort

    Next, create a measure to get the previous row:

    _prerow = 
CALCULATE (
    MAX ( 'Table'[Changed On Timestamp] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Sort]
            = CALCULATE (
                MAX ( 'Table'[Sort] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Sort] < MAX ( 'Table'[Sort] ) )
            )
    )
)

    Current Result:

    before.png

    When you add a new row to the table, the value of the previous row is still received successfully:

    after.png

    Attached a sample file in the next one, hopes to help you.

    Best Looks,
    Yingjie Li

    If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi, do you thing this collumn/measure will work with a BI that have 250.000 rows? 

      • v-yingjl's avatar
        v-yingjl
        Community Support
        5 years ago

        Hi Anonymous ,

        The calculated column and measure both should work.

         

        Best Regards,
        Community Support Team _ Yingjie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.