What is happening here is that you are using YEAR as a filter. So when you grab NULL, for example, you change the underlying table to look at only Hopkinton and Austin. New York is no longer part of anything being considered in any of the equations, even though your Select City parameter wants to look at New York.
Your [Distance from Origin] calc is a table calc. (Lookup is a table calc function.) Table calcs in a filter do not throw out rows in the table. All selected rows remain, and just the rows that fit the filter get displayed. But all the underlying rows are still there.
I would change YEAR to a parameter. (Note: Params limit you to only one selected value, but that's how you're using it on this dashboard, so it will work here.) You can have "All" as a value in your param list, and you'll just have to have some IF-logic to decide whether to get all data or a specific value.
See the attached. I made YEAR a parameter, and I added logic into the [Filter Distance] calc to incorporate the YEAR param. This calc is a table calc because it uses [Distance from origin], which is a table calc.
Radius Filter - SW v2 2.twbx 80.5 KB
As for your second part, if you make Select City as a filter instead of a parameter, you get the same problem. It would only grab the records for that city into the table. You want all cities in the table, but just display those that fit the criteria.
It works perfect!
For the second part, since we cannot make the "select city" as filter, is there any other way, where for the "select city" filter I can get only relevant data rather than getting all the cities displayed in the drop down?
I would be having countries all over the globe and it displays all the cities within that drop down, which is not an appropriate user experience. Is there any other work around?
I keep looking at this, and I can't figure out how/where you are using [Select City] on sheet 1. I'm baffled.
I might be able to see a different way to pare down a Select City list, but first I need to see how this is getting used. Can you point this out for me?
Sorry about that. If you see Dashboard 1, I have clubbed the two worksheets together. In future, I would be having many cities in the select filter parameter. The flow would be, I would select a country (for example US), then I select a city from "select City" parameter. In this case, only cities belonging to US should appear and not all cities. What can be done to achieve this?
Also, in case I have to add another filter say "Vacancy" filter, how can I add that in the "filter distance"? Once I "Select Radius" and after that I select "Year", I want to select a "Vacancy". How can I add a Vacancy filter/parameter?
That doesn't answer my question.
When I'm on Sheet 1, if I select Chicago, somehow it knows on the sheet to focus on Chicago. I can't see where that impacts the sheet. What calc or filter tells the sheet to use Chicago? It has to be getting picked up somewhere. And the [Distance from Origin] knows that the selected city is the origin. (Distance for that city is always zero, as we would expect.)
That's what I'm asking about.
When you are on Sheet 1, and if you select Chicago, then value in "Select City" parameter is Chicago.
When you pull the radius to say 500 miles, then, the "Distance from origin" formula calculates the distance of 500 miles from the origin city which is Chicago, which is displayed by "Filter Distance".
Does that answer your question?
I can see it working that way. I just can't find what calc or what filter or anything else that actually uses the value of the parameter. I need to find how it's being used so that I can see if I can find another way to get a value into the machinery.