Forum Discussion
Using data from one row for math applies to every row
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.
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.
6 Replies
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
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 ResultAh, 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.
