I'm trying to take a database of 3D points with 3 coordinates and a database with volumes that has min coordinates and max coordinates (6 coordinates / columns) and determine if the 3D point is in the selected 3D volumes.
The problem is rather complex but the issues i am having with powerpivot can be simplified to the following example.
Table 1 has one column of values 2, 13, 3 ,15 ,22
Table 2 has three columns, first column is range name, second is the start of the range and third is the end of the range.
<tbody>
</tbody>
I'd like to setup a pivot table showing the values in Table 1 that match the range names that are selected in a slicer next to it. I'd like to set it up so that multiple range names can be selected and it returns all values that are located in any of the ranges.
This formula works but it does not update based on the slicer selection
=if(calculate(SUM('Table'[Range Name]),filter('Table',[Value]>'Table'[Start Range]&&[Value]<'Table'[End Range]))>0,TRUE,FALSE)
To relate this example to my original problem I'd just need to do this three times for X, Y and Z coordinates. Note to further complicate my specific problem I also have a database of lines and volumes that I would like to relate to the reference volumes to determine if they are inside the volume, touch the volume or go through the volume.
Any help would be greatly appreciated.
The problem is rather complex but the issues i am having with powerpivot can be simplified to the following example.
Table 1 has one column of values 2, 13, 3 ,15 ,22
Table 2 has three columns, first column is range name, second is the start of the range and third is the end of the range.
Range Name | Start Range | End Range |
1 | 1 | 5 |
2 | 4 | 10 |
3 | 12 | 15 |
4 | 2 | 4 |
<tbody>
</tbody>
I'd like to setup a pivot table showing the values in Table 1 that match the range names that are selected in a slicer next to it. I'd like to set it up so that multiple range names can be selected and it returns all values that are located in any of the ranges.
This formula works but it does not update based on the slicer selection
=if(calculate(SUM('Table'[Range Name]),filter('Table',[Value]>'Table'[Start Range]&&[Value]<'Table'[End Range]))>0,TRUE,FALSE)
To relate this example to my original problem I'd just need to do this three times for X, Y and Z coordinates. Note to further complicate my specific problem I also have a database of lines and volumes that I would like to relate to the reference volumes to determine if they are inside the volume, touch the volume or go through the volume.
Any help would be greatly appreciated.