If statement for row difference

[sheshraja]

=IF(A2>A1,1,IF(A2=A3,0,0))

Hi

how do I write above statement in Power BI 

thank you

shesh

[harshnathani · Accepted solution]

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 Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[v-xicai · Accepted 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.

[Fowmy]

@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 🙂

YouTube, LinkedIn

[sheshraja]

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))

[harshnathani · Accepted solution]

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 Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[Fowmy]

@sheshraja 

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 🙂

YouTube, LinkedIn

[nandic]

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