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