Filtering postal codes and their related marketing data by zipcode

[Anonymous]

Hi everyone!

I'm currently working on a report which have the following purpouse, get the data related to a the zipcodes in a radius of X miles.  I've already used as an example the following report: https://community.powerbi.com/t5/Data-Stories-Gallery/Display-Points-within-a-Distance-Radius-on-a-P....

I've already got it working partially, as you can see.  The user selects a zipcode on the first dropdown slider, then select a radius on the second slider and then on the third slider, a list of the zipcodes on the radius selected by the user is shown.

Filter working

For this filter we used a geography dimension, which is related to a fact table with the marketing related data, and this fact is also related others dimensions (catalogs, time, etc.)

What we want is to use those zipcodes to filter the visualizations shown to the user, various lineal chart with marketing related data.  Those charts includes a metric as value and the full date as axis, only.

The issue is on the user experience: I know that the zipcodes can be selected manually, using Ctrl, but this is really tiresome when the list is too big.   The Select All option is just the same as not selecting anything, it just ignores the visual filter on the zipcode list based on the proximity measure.  I also know that the measures can't be used as a report filter. 

Do you know if there is some way to achieve my goal? Is it possible to create a measure that only shows the data from the filtered zipcodes?  E.g. Showing the revenue of the zipcodes on a radius of 5 miles from 90210.  Or I completly lost and my approach will not work? 

I've already tried the following (without success):

• Adding the Proximity measure as visual filter to the visualizations
• Using the Select All option on the slider
• Adding a custom measure for a metric using FILTER
• Adding a custom measure for a metric using TREATAS

Clicks Test = 
/* The proximity measure will be 1 if inside radius, 0 if not. */
var zipcodes = SELECTCOLUMNS(FILTER(dim_geography,[Proximity] = 1),"zipcode",dim_geography[zipcode])
VAR filter_zipcodes = TREATAS(zipcodes, 'dim_geography'[zipcode])
return CALCULATE(SUM(fct_marketing_data[Revenue]), filter_zipcodes)

[V-pazhen-msft]

@Anonymous 

Interesting topic, ideally it is achievable with what if parameter, but some test is required. Can you provide a sample pbix for us to test.

Regards

Paul

[Anonymous · Accepted solution]

I will submit a testing PBIX when I have free time.  Just wanted to say that using TREATAS on a measure worked as expected.  I was doing my test wrong.

I've just added a new measure for every metric that I need to graph:

Measure Metric = 
var zipcodes = SELECTCOLUMNS(FILTER(dim_geography,[Proximity] = 1),"zipcode",dim_geography[zipcode])
VAR filter_zipcodes = TREATAS(zipcodes, 'dim_geography'[zipcode]) 
return IF(HASONEVALUE(Selector[Zipcode]),CALCULATE(SUM(fct_marketing_data[metric]), filter_zipcodes),CALCULATE(SUM(fct_marketing_data[metric])))

I'll try to test if I can optimize the DAX code ( I think it's possible, I'll just read the documentation a bit more), but it's working right now, the user only select the source zipcode and the radius from a What If Parameter.