Accessing other column value of sliced table from unusually joined second table

[newmanity2]

I have the following Data table and its duplicate (Data Copy) which I've many-to-many joined on Type ID

 

The reason I've done this is because the user will be selecting a Type ID from a slicer in order to see each tower combination sharing this selection's Type ID, as shown here:

I then need to:

1. calculate the distance between the Selected Tower and each other Tower (sharing the Type ID) using the two Lat/Longs
2. identify the closest 3 other Towers to the selected Tower
3. display these 3 Towers and the Selected Tower on a map

For simplicity, we can assume the distance is ABS(Lat1 - Lat2) + ABS(Long1 - Long2).

My issue is that I cannot get the Data Lat/Long from within the Data Copy table because SELECTEDVALUE(Data[Tower ID]) doesn't work from within a calculated column.  I cannot use the measure either because I need to calculate this row by row.  I even tried creating a measure with a CALCULATEDTABLE variable within it, but I need the row entry results not just a single scalar returned.

Is there another way to build this to get what I need?  Thanks for any pointers!

[newmanity2]

Thanks for the suggestion, Amit.

I had a look at your video and I can replicate your example (by using two copies of the table) with Tower slicers on each of them and getting the data I need from the original table....that's a step in the right direction (I am getting the correct Lat/Longs for each Tower and can then calculate the distance and rank the closest).

LAT = SUM('Data'[Latitude])
LNG = SUM('Data'[Longitude])
LAT SELECTED = CALCULATE([LAT], FILTER(ALL(Data), Data[Tower ID] IN ALLSELECTED('Data 3'[Tower ID])))
LNG SELECTED = CALCULATE([LNG], FILTER(ALL(Data), Data[Tower ID] IN ALLSELECTED('Data 3'[Tower ID])))
LAT OTHER = CALCULATE([LAT], FILTER(Data, Data[Tower ID] IN ALLSELECTED('Data 4'[Tower ID])))
LNG OTHER = CALCULATE([LNG], FILTER(Data, Data[Tower ID] IN ALLSELECTED('Data 4'[Tower ID])))

However, I need to obtain the same results with just the single first slicer and have the second Tower Lat/Longs pulled in based on the shared Type.  You mentioned using an independent table for the Type but I'm not sure what you are suggesting...any chance you can elaborate?

[newmanity2 · Accepted solution]

Ok, so I finally got this working with a combination of Amit's suggestion and my original solution.  I added the many-to-many join between copied tables 3 & 4 above, and then changed ALLSELECTED to VALUES for the LAT / LNG OTHER measures as in the following, and everything works perfectly now.

Here is the pbix for anyone who is trying something similar...hope it helps (I'm too new to be able to upload it here)

LAT = SUM('Data'[Latitude])
LNG = SUM('Data'[Longitude])
LAT SELECTED = CALCULATE([LAT], FILTER(ALL(Data), Data[Tower ID] IN ALLSELECTED('Data 3'[Tower ID])))
LNG SELECTED = CALCULATE([LNG], FILTER(ALL(Data), Data[Tower ID] IN ALLSELECTED('Data 3'[Tower ID])))
LAT OTHER = CALCULATE([LAT], FILTER(Data, Data[Tower ID] IN VALUES('Data 4'[Tower ID])))
LNG OTHER = CALCULATE([LNG], FILTER(Data, Data[Tower ID] IN VALUES('Data 4'[Tower ID])))
DISTANCE = 
VAR blankVal = BLANK()
VAR DIST = 6378.7 * ACOS(
                        SIN([LAT SELECTED]/57.2958) * SIN([LAT OTHER]/57.2958) 
                        + COS([LAT SELECTED]/57.2958) 
                              * COS([LAT OTHER]/57.2958) 
                              * COS([LNG OTHER]/57.2958 - [LNG SELECTED]/57.2958)
)
VAR sv = SELECTEDVALUE('MAX DIST SLIDER'[Max Distance])
RETURN SWITCH( TRUE(),
    ISBLANK([LAT SELECTED]), blankVal,
    ISBLANK([LNG SELECTED]), blankVal,
    ISBLANK([LAT OTHER]), blankVal,
    ISBLANK([LNG OTHER]), blankVal,
    dist > sv, blankVal,
    TRUE(), dist
)

Final result:

[amitchandak]

@newmanity2 , I think that you should independent Type table with a distinct type

use the value in the independent slicer to get the values

example

refer

Compare Categorical Data Using Slicers, Compare Two Brands/Categories/Cities: https://youtu.be/exN4nTewgbc