searching for map coordinates with dax formula

[dan171]

Hi guys,
This one has being frustrating me for the last few days now.
So I have a simple set of x,y map coordinates in 2 columns of a table with a yes no result in a 3rd.
What I would like to be able to do is for each x,y result that is yes look at the square to north and see if that is a yes also.

For example if x=2,y=1 is yes find the row with x=3,y=1 and see if this is= yes

It would add to a new calculated column. And I would have 4 rows one for north, south, east and west.


I've upload a small example here. It has the data and then an example that works in excel to mimic the results I want.
example.xlsx (17,37 KB) - uploaded.net

if any one could help would be much appreciated!
Dan

 

[scottsen]

Assuming you put all the values of "valid" locations as X,Y columns in a power pivot table... you should be able to use LOOKUPVALUE to do what you want.

Easily the weirdest use of Power Pivot I have seen ...

 

[dan171]

Assuming you put all the values of "valid" locations as X,Y columns in a power pivot table... you should be able to use LOOKUPVALUE to do what you want.

Easily the weirdest use of Power Pivot I have seen ...

Yea it seems to be! Only been playing with it for a couple of weeks but it seems so useful! my friend is now thinking using GENERATE to make the 2nd table so don't have to copy the first one. but he has been playing with powerpivot for even less time!

my usual spreadsheets don't normally go further than IF functions!

It would be interesting to see if anyone has any otherways

 

[scottsen]

If I may ask... what is the end goal?

 

[dan171]

If I may ask... what is the end goal?

It is a environmental science project testing soil for contamination. the higher the connections so more blocks with 3 and 4s, means a bigger contamination.

haha, i have just realized that i am on a different board than where someone was suggesting an answer my friend came up a work around to have 2 identical tables that reference each other!

 

[scottsen]

So... for each location... you want to tally a score. 1 each for { N, S, E, W } ... 1 for itself? Anything for NE,SW,etc?

Once THAT is done (which doesn't seem horribly difficult) ... then what?

You should probably look into Power Map, to assign these values to a custom map? Still... I am curious what you will "do" w/ this score you calculate?

 

[dan171]

Nothing for NE SW etc yet wanted to see how well this worked first.
The grid we did was quite a large and is broken up in to zones. So I have tallied each row and they have a score of 0,1,2,3 or 4. I do a count of each (0,1,2,3 or 4)
and if i have mostly 0 or 1 in a zone it means the contamination is low/small if i have lots of 3 and 4 it means it is quite large.
I think it works well with just N, S, E & W.

Do you have any ideas on how to do it? i would ultimately like keep it one powerpivot thing and not have to copy.
here is a small example if your keen to test your skills!

ul.to/0myvo7s6

 

[scottsen]

I still say it's weird. but...

You can create 4 calc columns like:

[North]
=if(lookupvalue(Table1[positive], Table1[x], table1[x], Table1[y], Table1[y]-1) = "yes", 1, 0)

 

[dan171]

that looks like it is working. will let you know how i go in the big data set!

 

[ImkeF]

Hi there,
I have the feeling that this is what you've refered to: https://social.technet.microsoft.co...-of-two-subsets?forum=sqlkjpowerpivotforexcel

This is similar in that it searches one table for criteria in multiple lines. Different in that the lines belong to a specific day and the logic whether a match is found or not, should span a "given timeframe". So Scotts solution would be much more elegant than mine, provided that he could show us how to adopt that formula, so that it can take different dates within the relevant timespan into account (on the same client).

(&provided that the "given timeframe" Giles is referring to, can be clearly defined as month, week or so (otherwise one would need 1 column for each type of timespan))

So Scott, please have a look again on this