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.

Fowmy's avatar
Fowmy
Super User
6 years ago

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's avatar
nandic
Resident Rockstar
6 years ago

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