Using data from one row for math applies to every row

[JustinLowmaster]

I have a list of rows with XYZ coordinantes. I want to select one row (with a slicer?) and apply math using the XYZ of the selected row with every other row, limiting the visible rows to those that are <= 20 distance from the selected row.

SQRT((x1-x2)^2 + (y1-y2)^2 + (z1-z2)^2)

I can do it by making a measure where I manually put in the XYZ which I apply to a calculated column, and I have made measures that = the seleced XYZ, but it still won't apply the math on demand.

[Anonymous]

@JustinLowmaster , try this measure:

# Records within Distance =
VAR Distance_Limit = 20

VAR Selected_X = SELECTEDVALUE(TableName[X])
VAR Selected_Y = SELECTEDVALUE(TableName[Y])
VAR Selected_Z = SELECTEDVALUE(TableName[Z])

VAR All_Records = 
	ALL(TableName[<Slicer Value to select a sigle row>], TableName[X], TableName[Y], TableName[Z])

VAR Filtered_Records = 
	FILTER(
		All_Records
		,SQRT(
			(Selected_X - TableName[X]) ^ 2
			+ (Selected_Y - TableName[Y]) ^ 2
			+ (Selected_Z - TableName[Z]) ^ 2
		) <= Distance_Limit
	)

VAR Result = 
	COUNTROWS(Filtered_Records)

RETURN
Result

You store the selected XYZ coordinates in 3 variables.  This is based on choosing a single row via the slicer.

You then remove that single row filter from the 4 column table...so now all rows are visible within the filter context.

Iterating through each row, you compare the current row's XYZ coordinates to the selected coordinates via the distance formula...and only keep those that are within 20 (I made this a variable...much easier to change / update)

I set the Result to be counting the # of rows that remain.  When you have the Row ID put in a table visual with this measure, it should return only the rows that are within 20.

You can also use this measure as a Visual-Level Filter... set it >0.  That will filter the visual to only include the records that are within 20 of the selected row.

Hope this helps,

~ Chris H

[JustinLowmaster]

Thanks @Anonymous!

OK, I've got the measure and the slicer, and I apply and measure and ID (and readiable name) to the table, and I'm just getting the selected row and the # of rows that are <=20 away (which is the correct number for the one I've been focusing on).

I'm not grokking how to take that filtered rows result and make that a table of those rows. The slicer is limiting the table to just the selected row.

If I apply measure as filtere like mentioned, without the slicer interacting with the table, that doesn't work either.

Here's the specific code, in case I did something wrong there.

# Records within Distance = 
VAR Distance_Limit = 20

VAR Selected_X = SELECTEDVALUE('FACT - System Expansion'[X Coord])
VAR Selected_Y = SELECTEDVALUE('FACT - System Expansion'[Y Coord])
VAR Selected_Z = SELECTEDVALUE('FACT - System Expansion'[Z Coord])

VAR All_Records = 
	ALL('FACT - System Expansion'[System ID], 'FACT - System Expansion'[X Coord], 'FACT - System Expansion'[Y Coord], 'FACT - System Expansion'[Z Coord])

VAR Filtered_Records = 
	FILTER(
		All_Records
		,SQRT(
			(Selected_X - 'FACT - System Expansion'[X Coord]) ^ 2
			+ (Selected_Y -'FACT - System Expansion'[Y Coord]) ^ 2
			+ (Selected_Z - 'FACT - System Expansion'[Z Coord]) ^ 2
		) <= Distance_Limit
	)

VAR Result = 
	COUNTROWS(Filtered_Records)

RETURN
Result

[Cmcmahan · Accepted solution]

Ah, you have kind of a weird use case. Aggregating the results with COUNT is easy to display. Retaining all of the values to display is harder. If you're trying to return and display all the data points that are within 20 distance of a user-settable value, I would suggest using a What If Parameter.

Create separate What If Parameters for X, Y, & Z.  It will create a table and a slicer for each of them. Do NOT relate these tables to your fact table. And then you can use the measures the wizard creates (they should look like [X Value] if you named it "X") to create your distance measure instead of the vars.  

Here's the measure I would create:

Distance From Sliced Point = 
SQRT(
	([X Value] - SELECTEDVALUE('FACT - System Expansion'[X Coord])) ^ 2
	+ ([Y Value] - SELECTEDVALUE('FACT - System Expansion'[Y Coord])) ^ 2
	+ ([Z Value] - SELECTEDVALUE('FACT - System Expansion'[Z Coord])) ^ 2 
)

 Then you would take your table visual, add all the values you want to it, along with this measure.  Then you can filter the visual where [Distance From Sliced Point] is less than 20.  Slide your new parameter slicers to pick coordinates.

This has the added benefit of being able to pick any point in the coordinate plane, not just points where you have associated data.

[JustinLowmaster]

That works!

The XYZ values have up to 5 decimal places, which I'm not getting to work in the slicer boxes, just rounds to .00, but I'll fiddle with it, and that's probably a small margin for error.

[Cmcmahan]

You can get it to use decimals up to (I think) 15 places.  Go into the table expression, and manually edit it like so:

Also be sure to set the data type and format in the modeling bar to Decimal Number, and set the number of decimal places to 5, otherwise it will look like a bunch of zeroes.

[JustinLowmaster]

Just as an update, I found another solution:

I only need to check the XYZ based on certain rows, if a given group is present. 

What I did was use some of what I learned here from both sets of replies, but 'solved' it at the query level.

I made a new query that pulls only the XYZ coordinate rows needed. I then made measures of the sum of the XYZs separately.

On the page level, I sort using the table (not linked to any others) from the query and select one of the rows, filtering out the others.

Then I use 

pageSystem = 
SQRT(
	('Report Faction'[factionX] - SELECTEDVALUE('FACT - System Expansion'[X Coord])) ^ 2
	+ ('Report Faction'[factionY]  - SELECTEDVALUE('FACT - System Expansion'[Y Coord])) ^ 2
	+ ('Report Faction'[factionZ]  - SELECTEDVALUE('FACT - System Expansion'[Z Coord])) ^ 2 
)

and put that on the table, filter or <=20 and greater than 0, and there's my list!

Thanks @Cmcmahan and @Anonymous for the help in getting me to what worked for my specific needs. The XYZ sliders wasn't accurate enough, as GENERATESERIES(-200, +200, .00001) was too many values, and the sliders wouldn't accept an exact number. For a lesser range of values, it would work just fine.