Find rows that are not similar based on values

[JRadford]

Hello to anyone who can help, 

I have a table like the below. I'm looking into writing a DAX function or something. What I want is if the region and store are the same but the delta is negative, I want a new column to return the row that has the same Region and store with a different city that has a positive delta. 

Region	City	Store	Delta
NorthEast	New York	Antonios	-3
West	San Fransisco	Fashion	-6
West	Los Angeles	Rhode	3
NorthEast	New York	Macys	6
Midwest	Chicago	Torta	2
SouthEast	Atlanta	Walmart	-1
SouthEast	Atlanta	Walmart	8
NorthEast	New Jersey	Antonios	5
West	Los Angeles	Fashion	3
NorthEast	New York	Macys	8

The new column should state:

Region	City	Store	Delta	City to pull from
NorthEast	New York	Antonios	-3	New Jersey
West	San Fransisco	Fashion	-6	Los Angeles
West	Los Angeles	Rhode	3
NorthEast	New York	Macys	6
Midwest	Chicago	Torta	2
SouthEast	Atlanta	Walmart	-1	Savannah
SouthEast	Savannah	Walmart	8
NorthEast	New Jersey	Antonios	5
West	Los Angeles	Fashion	3
NorthEast	New York	Macys	8

[rajendraongole1 · Accepted solution]

Hi @JRadford - Please find the attached pbix file 

use a calcualted column and the logic as below:

City to pull from =

VAR CurrRegion = Inputdata[Region]

VAR CurrStore  = Inputdata[Store]

VAR CurrDelta  = Inputdata[Delta]

VAR PosCity =

    CALCULATE (

        MIN ( Inputdata[City] ),

        FILTER (

            Inputdata,

            Inputdata[Region] = CurrRegion &&

            Inputdata[Store] = CurrStore &&

            Inputdata[Delta] > 0

        )

    )

RETURN

    IF ( CurrDelta < 0, PosCity, BLANK () )

Hope this helps.