If condition to compare 2 rows and 2 columns and create a flag in 3rd column.

[itsmeanuj]

Hi,

I have 3 columns sorted on Product and Order#. I want to create a flag (4th column) based on this condition which works in excel like this -> =IF(AND(A2=A1,B2=B1,C2<>C1),1,0). I am not sure how to implement this I Power Query Editor in PBI on large set of data. Can someone please help?

Thanks,

Anuj

[v-easonf-msft · Accepted solution]

Hi, @itsmeanuj 

Could you please tell me whether your problem has been solved?

You can also try a formula as below:

flag =
VAR current_index = 'Table'[Index]
VAR pre_product =
    CALCULATE (
        MAX ( 'Table'[Product] ),
        FILTER ( 'Table', 'Table'[Index] = current_index - 1 )
    )
VAR pre_Order =
    CALCULATE (
        MAX ( 'Table'[Order#] ),
        FILTER ( 'Table', 'Table'[Index] = current_index - 1 )
    )
VAR pre_lot =
    CALCULATE (
        MAX ( 'Table'[LOT] ),
        FILTER ( 'Table', 'Table'[Index] = current_index - 1 )
    )
RETURN
    IF ( [Product] = pre_product && [Order#] = pre_Order && [Lot] <> pre_lot, 1, 0 )

Best Regards,
Community Support Team _ Eason

[amitchandak]

@itsmeanuj , for that you need an index column https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

then new column in DAX

= var _prd = maxx(filter(Table,[Index] =earlier([Index]) -1) , [Product])

var _ord = maxx(filter(Table,[Index] =earlier([Index]) -1) , [Order#])

var _Lot= maxx(filter(Table,[Index] =earlier([Index]) -1) , [Lot])

retrun

if([Product]=_prd && [Order #] =_ord && [Lot] <> _lot,1,0)