Forum Discussion
Count and Inverse Count based on Parameter Range
I have a heat map where I'm tracking the number of people whose zip code falls within AND outside a certain distance (a parameter value) from a separate table of store locations.
I have a table of individuals and their zip codes and a measure that calculates the minimum distance between them a list of store locations in a separate table (based on latitude and longitude). I then have a measure called Radius Flag that returns 1 or 0 based on whether the minimum distance is less than the Range Value.
My goal is to return a table with the count of people both INSIDE and OUTSIDE the particular radius, with the rows being the markets associated with zip codes (MSA). My issue is mainly with the value outside the radius. I've tried a variety of combinations of code within my Employee Count measure but can't seem to get the counts for both within and outside the range to work in the same table. The table in the visual seems to only work with the dynamic Store Range slide if I set a filter on the Radius Flag. This then obviously prevents me from reflecting the opposite values in the same table.
Any help would be greatly appreciated!!
4 Replies
EHP829 Here's an inverse aggregator and inverse selector that may help: Inverse Aggregator - Microsoft Fabric Community, Inverse Selector - Microsoft Fabric Community
Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.I've read through the inverse aggregator articles and the common issues and couldn't find anything to help me understand why my measures aren't working.
If I have a list of people and their zip codes (fake in this example) and a list of stores and their zip codesID Zip A '00001 B
'00009 C '00003 D '00009 E '00001 Stores Zip M '00005 N '00006 O '00001 P '00005 I already have a measure that calculates the minimum distance between an individual and any of the stores in the list. The store lists and employee lists are very large so a calculated column needs to be avoided.
I have a radius flag measure that determines if an individual's minimum distance between themselves and any store is less than the dynamic range (see previous photos).
If, for the sake of this example, the range is set to 2 miles, (and we assume the distance between any of those fake zip codes is given by the difference in the last digit), I'd expect the following result:
Number of People Within Range Number of People Out of Range 3 2 where persons A, C, and E were within range and persons B and D were outside the range.
Does this provide enough information to describe my scenario?
I've read through the inverse aggregator articles and the common issues and couldn't find anything to help me understand why my measures aren't working.
If I have a list of people and their zip codes (fake in this example) and a list of stores and their zip codesID Zip A '00001 B
'00009 C '00003 D '00009 E '00001 Stores Zip M '00005 N '00006 O '00001 P '00005 I already have a measure that calculates the minimum distance between an individual and any of the stores in the list. The store lists and employee lists are very large so a calculated column needs to be avoided.
I have a radius flag measure that determines if an individual's minimum distance between themselves and any store is less than the dynamic range (see previous photos).
If, for the sake of this example, the range is set to 2 miles, (and we assume the distance between any of those fake zip codes is given by the difference in the last digit), I'd expect the following result:
Number of People Within Range Number of People Out of Range 3 2 where persons A, C, and E were within range and persons B and D were outside the range.
Does this provide enough information to describe my scenario?Greg_Deckler I'm sorry I didn't tag you in my response!
