Help with using measures to filter geo results

[shamishanc]

I have three tables that I want to get talking with one another:

1. Member geographic information (geolocated)
2. Census Sub-Division data (geolocated)
3. Province/Territory index (acts as bridge for relationships with other two tables)

I also have a formula that calculates distance between two points (that I took from another post in this community - thanks!):

Distance_From_Members = 
var Lat1 = MIN('Members'[Latitude])
var Lng1 = MIN('Members'[Longitude])

var Lat2 = MIN('Census'[latitude])
var Lng2 = MIN('Census'[longitude])
---- Algorithm here -----
var P = DIVIDE( PI(), 180 )
var A = 0.5 - COS((Lat2-Lat1) * p)/2 + 
    COS(Lat1 * p) * COS(lat2 * P) * (1-COS((Lng2- Lng1) * p))/2
var final = 12742 * ASIN((SQRT(A)))
return final

So far, I'm able to create a table visualization that lists all of the members within x kilometers of a census sub-division. I end up with a table that looks something like this:

Census Subdivision | Province | Member | Distance

Toronto                  | Ontario  | Member 1 | 25.4

Toronto                  | Ontario  | Member 2 | 16.2

Montreal                | Quebec  | Member 3 | 24.2

Montreal                | Quebec  | Member 4 | 52.4

...and so on, to the point where I can't even export the data to Excel because the dataset is too large (I tried doing provincial slices, but no dice, so to speak).

I get lossy data if I try to show a member count instead of a list, or even if I only ask for the first member for each census subdivision.

Also, I can only visualize the data in this way. I'm not sure how to use the measure as a filter to apply to other visualizations (I understand measures can't be used as filters and I'm not sure how to create Calculated Tables using measures to filter what makes it into the table). I would love to be able to slice the data in a number of ways based on the distance between communities and members.

I would appreciate any guidance you can offer.

[shamishanc · Accepted solution]

Just as an update - I solved this problem quite easily (so much for giving up :P).

I used the following formula:

NewTable = CROSSJOIN(Table1,Table2)

Simple as that. I then built my distance measure into that new table, and then turned the measure results into a static value with:

NewColumn = DistanceMeasure

There was some tweaking (I had to create new column headers for some fields, but ultimately it was a painless process.