IF formula taking into account a condition from cell located in row above.

[Anonymous]

Hi All,

I am relatively new to Power Bi and I am struggling into creating an IF formula with multiple conditions where one of the conditions is to consider a value located in the row above. I saw in one of the discussions that I should create and Index column and include it in my IF formula but I am not to sure how it works.

My formula so far looks like this: Answer= IF('Table1'[Response]="1" &&... Here I would like to add the other condition which refers to the cell in the row jsut above. Then based on both these conditions the return value would be equal to the value of a particular cell within the same row. 

Hope you can help me and thank you in advance.

[v-yuta-msft · Accepted solution]

@Anonymous,

Based on your description, suppose your table is like below and you want to achieve the value in above row which meets your requirement.

 

Yes, you need to create an index column first, click query editor-> add columns-> index column, then apply and close query editor. Create a calculate column using DAX like pattern below:

Answer = 
VAR index_above_row = Table1[Index]
RETURN
    IF (
        Table1[Response] = 1
            && Table1[Response]
                = CALCULATE (
                    MAX ( Table1[Response] ),
                    FILTER ( Table1, Table1[Index] = index_above_row - 1 )
                ),
        CALCULATE (
            MAX ( Table1[Value] ),
            FILTER ( Table1, Table1[Index] = index_above_row - 1 )
        ),
        "Not Found"
    )

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Yes this is exactly the logic I need. Thank you so much!