Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 "
Solved! Go to Solution.
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:
When you add a new row to the table, the value of the previous row is still received successfully:
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.
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:
When you add a new row to the table, the value of the previous row is still received successfully:
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.
Hi, do you thing this collumn/measure will work with a BI that have 250.000 rows?
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.
@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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |