Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gsmiro
Regular Visitor

Get all rows not included in filter/slicer

I have the following table

 

NameIdLatLonRadioRadioIdRadio.LatRadio.LonDistanceType
Place 1c115.xxxx31.xxxxRadio 1r115.444432.666635Place
Radio 1r115.444432.6666Radio 1r115.444432.66660Radio
....         

 

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.

 

 

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

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.

2.JPG

 

 

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

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

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.

2.JPG

 

 

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

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.Screenshot (323).pngScreenshot (324).pngScreenshot (325).png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.