Forum Discussion
If statement for row difference
HI sheshraja ,
Add an index column in Power Query.
Then Create a Calulated Column
Resultant Column = var prev= CALCULATE(MIN('Table'[A]),FILTER('Table','Table'[Index] = EARLIER('Table'[Index])-1)) RETURN if ('Table'[A] - prev = 1 , 1 , 0)Regards,
Harsh NathaniAppreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!Hi sheshraja ,
You may enter into Query Editor via "Transform data" tab under Home ribbon, add an Index column under "Add column" ribbon, click "Close & Apply" button.
Then you may create calculated column like DAX below.
Result = var _PreRow= CALCULATE(MAX(Table1[A]), FILTER(Table1, Table1[Index]< EARLIER(Table1[Index]))) return IF(Table1[A] =_PreRow, 0, Table1[A] )Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sheshraja
How do calculate ZERO in for both true and false in your formula.
IF(A2=A3,0,0)
Can you explain what calculation you are after?
IF Document:
https://docs.microsoft.com/en-us/dax/if-function-dax
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
I accept KUDOS 🙂
Hi
thank you for the reply the furmula works on excel, I would like to do the same on power BI as custom column.
for each repating secquence of No. 1, I would like to see just one on the result column as shown below.
| A | B (RESULT COLUMN) | |
| 0 | 0 | IF(A3>A2,1,IF(A3=A4,0,0)) |
| 1 | 1 | IF(A4>A3,1,IF(A4=A5,0,0)) |
| 1 | 0 | IF(A5>A4,1,IF(A5=A6,0,0)) |
| 1 | 0 | IF(A6>A5,1,IF(A6=A7,0,0)) |
| 0 | 0 | IF(A7>A6,1,IF(A7=A8,0,0)) |
Mmmm. I suggest you do it in Power Query
let me know if it will work for you.I am sure you will have other columns as well in your dataset, share a realistic sample.
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
I accept KUDOS 🙂
Hi sheshraja ,
In Power Query you can add index column and based on that index column use lookup function to move between rows.
Example formula:Result =
IF (
Sheet1[A1]
> LOOKUPVALUE ( Sheet1[A1], Sheet1[Index], Sheet1[Index] - 1 ),
1,
IF (
Sheet1[A1]
= LOOKUPVALUE ( Sheet1[A1], Sheet1[Index], Sheet1[Index] + 1 ),
0,
0
)
)
Cheers,
Nemanja
HI sheshraja ,
Add an index column in Power Query.
Then Create a Calulated Column
Resultant Column = var prev= CALCULATE(MIN('Table'[A]),FILTER('Table','Table'[Index] = EARLIER('Table'[Index])-1)) RETURN if ('Table'[A] - prev = 1 , 1 , 0)Regards,
Harsh NathaniAppreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
