Forum Discussion
If statement for row difference
=IF(A2>A1,1,IF(A2=A3,0,0))
Hi
how do I write above statement in Power BI
thank you
shesh
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.
6 Replies
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 ,
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.
