I have a PowerBI dashboard that contains casino guest information, including their latitude and longitude coordinates. The dashboard allows a user to input a zip code and select a radius around that zip (slicers). When doing so, it filters the table of casino guests to only those with a distance (measure) less than or equal to the radius chosen. I used this distance measure and logic to create another measure "InRadius" that would show 1 or 0 depending on if the given guest is within the radius. then used this "InRadius" measure as a filter for my table, as well as other visuals. everything works fine, except I am wanting to add a "between" slicer that will filter guest's ADW Range and I am unable to filter this slicer by my measure "InRadius", therefore my min and max values for ADW in the slicer, do not accurately reflect the ADW ranges for guests filtered in my table. Is there any way for me to get around this? Is it possible to create a new measure that I could use specifically for this slicer? Is it possible to filter a slicer by only the values present in an already filtered table? In general, I am just wanting to create a slicer that filters a columns values based on a measure's value, but can't figure out how to go about accomplishing it.
some codes I have tried while creating a new measure but haven't given desired result:
InRadiusADWFilter = IF([InRadiusPatron]=1, VALUES(TF_MapRadius[ADWNet]))
InRadiusADWFilter = FILTER(VALUES(TF_MapRadius[ADWNet]), [InRadiusPatron]=1)
InRadiusADWFilter = CALCULATE(VALUES(TF_MapRadius[ADWNet]), [InRadiusPatron]=1)
Here is my code for my measure distance:
// Haversine/great-circle distance calculation adapted from Stack Overflow: https://stackguides.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-haversine-formula
VAR __latSelected = SELECTEDVALUE('TF_MapRadius'[maplat])
VAR __lonSelected = SELECTEDVALUE('TF_MapRadius'[maplong])
VAR __radius = 3956
VAR __multiplier = PI()/180
VAR __latDiff = (MIN('Selector'[Latitude])-__latSelected) * __multiplier
VAR __lonDiff = (MIN('Selector'[Longitude])-__lonSelected) * __multiplier
VAR __formula1 =
SIN(__latDiff/2) * SIN(__latDiff/2) +
COS(MIN('Selector'[Latitude]) * __multiplier) * COS(__latSelected * __multiplier) *
SIN(__lonDiff/2) * SIN(__lonDiff/2)
VAR __formula2 = 2 * ATAN(DIVIDE(SQRT(__formula1),SQRT(1-__formula1)))
VAR __distance = __radius * __formula2
RETURN __distance
here is my code for the measure "InRadius":
InRadiusPatron = if('TF_MapRadius'[Distance2] < Radius[Radius Value],1,0)
I've created dummy table that represents my filtered table:
| ID | PatronName | ADWNet | Distance | InRadius |
|---|---|---|---|---|
| 1 | Smith, John | 325,555 | 5.2 | 1 |
| 2 | Jobs, Steve | 200 | 7.0 | 1 |
| 3 | Jeter, Derek | 90,000 | 10.1 | 1 |
| 4 | Jordan, Michael | -2,567 | 14.5 | 1 |
