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