How to get a table of filtered values using DAX?

[dylanae2s]

I have a table of cities which shows their monthly water rates. I created a dashboard so that the user can filter the cities to be within a certain distance. For example you can select a city and then choose to filter it by only showing the average water rates of cities within a 100 mile distance. What I can't figure out how to do is getting a table that shows the filtered data. Lets say it's just a table with two columns, City and water rate. How do I get it so that the table only shows the cities and water rates that are within those 100 miles?

[v-eqin-msft]

Hi @dylanae2s ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @dylanae2s ,

To my knowledge, it is not possible to create a calculate column/table that could change dynamically with user selections such as slicers on the report. You could create measure instead.

If you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file without sensitive data from your Onedrive for Business.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@dylanae2s I would recommend sharing the sample pbix file with the expected output and I can get back to you with the solution. Remove any sensitive information before sharing.

[parry2k]

@dylanae2s you need lat and lng for the cities to make it works? Do have that data? It will help to the calculated the distance between cities and then anything above 100 miles can be filtered. So lat and lng are important to make it work.

[dylanae2s]

Yes I have that data. I made a slicer so that I can select the range that I want to filter within. So far I have made cards that show for example the average water rate within the selected radius. However what I can't figure out how to do is making a table that shows the filtered values. For example if there are 3 cities within that distance I want a filtered table that shows City 1,City 1 water rate, City 2, City 2 water rate, etc. I can't figure out how to make that table