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
SANGST
Helper I
Helper I

Filtering a table based on Map selection

I've seen similar threads based on slicer selection, but belieive this is sufficiently different to warrent a seperate thread (at least the dax solutions for a slicer don't seemt to apply here).

 

I have a map and a table, both contain the same data (locations, sales). When i click on a plot on the map (say, 'Atlanta') it will filter my table to all of the sales in Atlanta. This is the desired outcome.

 

However, when nothing is selected on the map, the table shows all rows (all sales from all locations). How can i set the table default to be to display no rows, untill a point on the map is selected?

 

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

Hi, @SANGST 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end. 

Table:

c1.png

 

Map:

c2.png

 

You may create a measure as below.

Visual Control = 
IF(
    ISFILTERED(Map[Location]),
    IF(
        SELECTEDVALUE('Table'[Location]) in DISTINCT(Map[Location]),
        1,0
    ),
    0
)

 

Finally you need to put the measure in the visual level filter to get the result.

c5.png

 

c3.pngc4.png

 

Best Regards

Allan

 

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

7 REPLIES 7
jbaisley
Helper I
Helper I

This is very similar to my problem. What would you need to do to change the original binary look up to flag distinct Sales persons, but keeping your location selection as the primary slicer?

v-alq-msft
Community Support
Community Support

Hi, @SANGST 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end. 

Table:

c1.png

 

Map:

c2.png

 

You may create a measure as below.

Visual Control = 
IF(
    ISFILTERED(Map[Location]),
    IF(
        SELECTEDVALUE('Table'[Location]) in DISTINCT(Map[Location]),
        1,0
    ),
    0
)

 

Finally you need to put the measure in the visual level filter to get the result.

c5.png

 

c3.pngc4.png

 

Best Regards

Allan

 

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

I'm not sure what you mean by "Finally, put the measure in a visual level filter."  Can you explain that more?  Does that mean I need to create a separate measure called "Visual Control"?

 

Lot's of answers here which i'm sure would work, so thank you all.

This answer in particular with the attached figures and pbix as an example were very easy to follow and exactly what was needed. Thanks

dedelman_clng
Community Champion
Community Champion

Hi @SANGST -

 

As long as you don't have the location in the table visual, you can use this

 

IF( HASONEVALUE( Map[Location] ), [Your Measure] )

 

Then make sure "Show Items with No Data" is unchecked for all non-measure values in the table visual.

 

If you do need the name of the location in the table, I will need to think further on this.

 

Hope this helps

David

Thanks @dedelman_clng unfortunatley I wasn't able to implement that - the Dax didn't seem to recognise 'map' and i wasn't sure what 'location' and 'your measure' should refer to in my context? Could you expand on that explanation for me please?

 

My map contains hundreds of points (every U.S. city), and all that is plotted is the sum of sales at each location.

The table has a column for sales person and a second for their sales. When you click a location on the map, the table shows you which sales people had sales at that location, and the value.

But when nothing is selected on the map (so all points displayed on the map) i'd like the table to be blank, rather than showing all sales all sales people. I hope that makes sense? 

 

Thank you

 

 

IF( HASONEVALUE( Map[Location] ), [Your Measure] )

 

IF - DAX command, used as IF( Condition, Value if True, Value if False)

 

HASONEVALUE - DAX command, used as HASONEVALUE( Table[Column] ) - returns True if the column in questino has only one value based on all selections, slicers, filters, etc; returns False otherwise

 

Map[Location] - the Table[Column Name] that contains the name of the locations that are plotted on the map

 

[Your Measure] - Whatever you want to calculate to put into the "Value" on your table visual.

 

Hope this helps

David

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.