Forum Discussion

sheshraja's avatar
sheshraja
Regular Visitor
6 years ago
Solved

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  
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
    v-xicai
    6 years ago

    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's avatar
sheshraja
Regular Visitor
6 years ago

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.

 

AB  (RESULT COLUMN) 
00IF(A3>A2,1,IF(A3=A4,0,0))
11IF(A4>A3,1,IF(A4=A5,0,0))
10IF(A5>A4,1,IF(A5=A6,0,0))
10IF(A6>A5,1,IF(A6=A7,0,0))
00IF(A7>A6,1,IF(A7=A8,0,0))
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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!