Get all rows not included in filter/slicer

[gsmiro]

I have the following table

Name	Id	Lat	Lon	Radio	RadioId	Radio.Lat	Radio.Lon	Distance	Type
Place 1	c1	15.xxxx	31.xxxx	Radio 1	r1	15.4444	32.6666	35	Place
Radio 1	r1	15.4444	32.6666	Radio 1	r1	15.4444	32.6666	0	Radio
....

where each line which consists of a place - radios and points of interest - with their lat/lon, and a radio with each lat/lon. For each entry Distance is a calculated column and Type tells whether it is a POI or a radio.

I'm using it to display radios and affected POI's within a certain range in a MAP visualization. Since I need to show both radios and POI's in the map, there is one line for each radio and it's distance to all other radios as well.

The map has a filter for Distance <= 60 and there's alos a multi-select dropdown slicer applied to Radio column.

I need to show a second map that shows all POI's that are not selected either by the map Distance filter or the slicer. I tried using creating a second table like this

Other = EXCEPT(ALL(Distances),Distances)

but no matter what selection I make in the first map, the second map is always empty.

Thank you for the help.

[v-lili6-msft · Accepted solution]

hi  @gsmiro 

First, you should know that:

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Second, for your case, you could try this way:

Step1:

Create duplicate table for basic table.

Step2:

Create a second map visual by this duplicate table.

Step3:

Create a measure that  as below:

For filter = MAXX(EXCEPT(VALUES('Duplicate table'[Radio]),VALUES(Distances[Radio])),[Radio])

Then drag it into second map visual level filter and set filter is not blank.

here is a simple sample pbix file, pleae try it.

Regards,

Lin

[jay_patel]

Hi @v-lili6-msft 

Did the same steps as you suggested but unfortunately i am not getting the result.Please find the attached screenshot and correct me if i am wrong.