Map visual Filtering - Tooltip

[Foxxon28]

Dear reader,

For a specific business request I need to dynamically filter data clusters within a Power BI Map visual.

My data setup is as follows:

Building_ID	Fire_Hazard_Building_ID	Longitude	Latitude	Distance_to_Hazard_meter
1	3	x	y	10
2	3	x	y	15
3	3	x	y	0
4	5	x	y	8
3	5	x	y	20
5	5	x	y	0

 What I need to do is count all the building_Ids, for each fire_hazard_building_Id cluster. That way each bubble contains the count of the cluster, based on filter context given.

Where users can filter on "amount witin Fire_Hazard_group" / "Distance to Fire_Hazard". I have done this quite easily within a matrix, grouping Building_ID and Fire_Hazard_Building_ID, but this doesn't work for the Map Visual, as each builing is indivudually located and calculated on the map.

I want end-users to be able to filter on "Group amount" (and more, not included in this sample data) based on a filter/slicer. 

Since all the map visual points are individual, I cannot seem to get a group count, based on the Fire_hazard_Building_ID.

Another 'challenge' is that neither ID column is unique, as 1 ID can have multiple risks. 

Any thoughts?

If you need more information, please let me know.

Thanks!
Daniël

[PavanLalwani]

To address your need of dynamically filtering data clusters within a Power BI Map visual based on the `Fire_Hazard_Building_ID` and counting the number of buildings in each hazard cluster, while also allowing users to filter based on the distance and other criteria, here’s a step-by-step approach:

### Steps to Create a Dynamic Map with Group Count Filtering:

1. **Aggregate Data for the Map Visual**
The problem stems from the fact that the map visual plots individual data points, but you want to show aggregated counts for clusters based on `Fire_Hazard_Building_ID`. For this, you need to create a calculated column or measure that groups and counts the buildings for each hazard.

You can create a measure to count the buildings within each `Fire_Hazard_Building_ID` cluster:

```DAX
BuildingCountByHazard =
CALCULATE(
COUNT('Data'[Building_ID]),
ALLEXCEPT('Data', 'Data'[Fire_Hazard_Building_ID])
)
```

This measure counts all `Building_IDs` that are associated with each `Fire_Hazard_Building_ID`.

2. **Create a Measure for Dynamic Filtering Based on Distance**
If you want users to filter based on distance, create a measure that calculates the number of buildings within a certain distance from the hazard. You can achieve this with a measure like this:

```DAX
BuildingWithinDistance =
CALCULATE(
COUNT('Data'[Building_ID]),
FILTER('Data', 'Data'[Distance_to_Hazard_meter] <= SELECTEDVALUE('Distance Filter Table'[Distance]))
)
```

This will allow users to select a distance via a slicer, and the measure will count buildings within that distance for each hazard.

3. **Use Tooltips for Additional Information**
Since the map visual plots individual points, you can use tooltips to display the group count of buildings within each hazard. Add your `BuildingCountByHazard` measure to the **Tooltips** field in the map visual. This way, when users hover over a hazard point, they will see the total count of buildings in that hazard cluster.

4. **Dynamic Slicer for Group Amount and Distance**
To filter clusters based on the number of buildings or distance, create slicers using measures and calculated columns:

- **Group Amount Slicer**: Create a measure for group amounts:
```DAX
GroupAmount =
CALCULATE(
COUNT('Data'[Building_ID]),
ALLEXCEPT('Data', 'Data'[Fire_Hazard_Building_ID])
)
```
Then, use this measure in a **Slicer** to allow users to filter clusters based on the number of buildings.

- **Distance Slicer**: Create a distance slicer using the `Distance_to_Hazard_meter` column to filter based on proximity to the hazard.

5. **Map Visual Setup**
- Add the `Latitude` and `Longitude` fields to the **Map** visual.
- Use the `BuildingCountByHazard` as the size of the bubbles (in the **Size** field).
- Add your `BuildingCountByHazard` and `BuildingWithinDistance` measures to the **Tooltips** field to show the group counts and buildings within the filtered distance.

6. **Use the Matrix for Cross-Filtering**
To provide users with both a visual and matrix-based view, keep the matrix visual. When the user clicks on a hazard or group in the matrix, it will cross-filter the map visual, displaying only the relevant points or clusters.

### Additional Considerations:
- **Non-Unique IDs**: Since you mentioned that the `Building_ID` and `Fire_Hazard_Building_ID` columns are not unique, make sure you are using `COUNTROWS` or `DISTINCTCOUNT` where needed in your DAX formulas to handle duplicates.

- **Multiple Risks for One ID**: You might need to further filter or handle multiple risks by creating a new table or relationship that associates buildings with multiple risks to ensure you're grouping them correctly.

This setup will enable you to display dynamic groups on the map visual, filtered by both the group size and the distance to hazards, with tooltips showing the cluster size for each hazard point.

[Foxxon28]

Unfortunately this has the same result as the solution provided  by @Kedar_Pande 

[Kedar_Pande]

@Foxxon28 

Create a Measure:

BuildingCount =
CALCULATE (
COUNTROWS ( 'YourTable' ),
ALLEXCEPT ( 'YourTable', 'YourTable'[Fire_Hazard_Building_ID] )
)

Use the BuildingCount measure as the size of the bubbles on the map.

Add slicers for relevant fields (e.g., Distance_to_Hazard_meter).

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Foxxon28]

Thanks for the swift reply!

However this doesn't work when I don't have anything pre-filtered. If no filters are applied it seems to count all the Fire_hazard_id rows. Only for when I pre-filter a specific Fire_Hazard_Building_ID, it correctly counts the cluster. Whereas I want to calculate it beforehand.

[Kedar_Pande]

What is expected when no filters are applied?

[Foxxon28]

When no filters are applied I would expect it to count the Building_Id's for it's MIN Fire_Hazard_Building_ID.

For example as per my previous sample data:

If I were to hover over Building_ID = 4, it would give me a count of 3 (Building_ID = 4 has Fire_Hazard_ID = 5. There are 3 buildings with Fire_Hazard_Buildig_ID = 5)

[Anonymous]

Hi @Foxxon28 ,

If I were to hover over Building_ID = 4, it would give me a count of 3 (Building_ID = 4 has Fire_Hazard_ID = 5. There are 3 buildings with Fire_Hazard_Buildig_ID = 5)

It looks like you are setting the Building_ID field as a slicer or filter? Then you can try the following DAX:

Measure = 
IF(
    ISFILTERED('Table'[Building_ID]),
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Building_ID] ),
        ALLEXCEPT ( 'Table', 'Table'[Fire_Hazard_Building_ID] )
    ),
    CALCULATE(
        DISTINCTCOUNT ( 'Table'[Building_ID] ),
        'Table'[Fire_Hazard_Building_ID] = MINX(ALL('Table'), 'Table'[Fire_Hazard_Building_ID])
    )
)

I don't know how you created your map visual object, so I can only provide you with DAX but cannot help you test it. If the result is incorrect or you have more than one filter, please describe your visual object creation process and all the filter fields used in detail, thank you!

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

[Foxxon28]

Hi! 

Thank you for the reply.

Sadly this is not what I am looking for. I will try to give you some more context:

The visual is simply build with longitude/latitude and on the Legend a "Risk or not Risk". That way it colors Risk buildings in red and none Risk buildings blue.

What I currently want to achieve is if nothing is filtered and I hover over one of the bubbles, that I want to see the group count of one of its Fire_hazard_building_id clusters. The previous provided solutions only show the count of the whole dataset, as if there is only 1 Fire_Hazard_building_id.

[Anonymous]

Hi @Foxxon28 ,

Here is my sample data:

Please try this DAX:

BuildingCount = 
VAR _table = 
CALCULATETABLE(
    VALUES('Table'[Fire_Hazard_Building_ID]),
    ALL('Table'),
    'Table'[Building_ID] = SELECTEDVALUE('Table'[Building_ID])
)
RETURN
CALCULATE(
    DISTINCTCOUNT('Table'[Building_ID]),
    ALL('Table'),
    'Table'[Fire_Hazard_Building_ID] IN _table
)

And the final output is as below:

But please note that because when a measure is placed in a visual object, it will be affected by the context filter. In order to eliminate this effect, I had to use the ALL function in DAX, but this will cause your other filters to be invalid. If you need to have other filters, then DAX will also need to be changed according to all the filters you need to apply.

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

[Foxxon28]

We seem to be getting somewhere. Though it does in fact need filtering based on the "distance_to_Hazard" column.

Also I think it is currently adding up something. As per your example I would expect the Count amount for Building ID = 3 to be 3. There are 3 buildings that have Fire_Hazard_Building_Id =3.

Building_ID = 2 should return 0, as there are no Fire_Hazard_Building_ID's with value 2, therefore there is no risk. 

Does this make it more clear?

Thanks!,

Daniël

[Anonymous · Accepted solution]

Hi @Foxxon28 ,

If you want to follow your current requirement:

As per your example I would expect the Count amount for Building ID = 3 to be 3. There are 3 buildings that have Fire_Hazard_Building_Id =3.

Building_ID = 2 should return 0, as there are no Fire_Hazard_Building_ID's with value 2, therefore there is no risk. 

You can change the DAX into this:

BuildingCount_2 = 
VAR _ID = SELECTEDVALUE('Table'[Building_ID])
VAR _Count = CALCULATE(
    COUNTROWS('Table'),
    ALL('Table'),
    'Table'[Fire_Hazard_Building_ID] = _ID
)
RETURN
IF(
    _Count = BLANK(),
    0,
    _Count
)

And the output:

Considering your needs:

Though it does in fact need filtering based on the "distance_to_Hazard" column.

I suggest you first use this DAX to create a calculated table:

Slicer = VALUES('Table'[Distance_to_Hazard_meter])

Use this table to create a slicer, and change the measure BuildingCount_2 into this:

BuildingCount_2 = 
VAR _ID = SELECTEDVALUE('Table'[Building_ID])
VAR _Count = CALCULATE(
    COUNTROWS('Table'),
    ALL('Table'),
    'Table'[Fire_Hazard_Building_ID] = _ID && 'Table'[Distance_to_Hazard_meter] IN VALUES(Slicer[Distance_to_Hazard_meter])
)
RETURN
IF(
    _Count = BLANK(),
    0,
    _Count
)

Final output:
If I select distance_to_Hazard from 0 to 10:

The count change from 3 to 2.


But I'm a little surprised that your current demand does not seem to have the same meaning as your previous demand:

If I were to hover over Building_ID = 4, it would give me a count of 3 (Building_ID = 4 has Fire_Hazard_ID = 5. There are 3 buildings with Fire_Hazard_Buildig_ID = 5)

Didn't you want to find out how many different Building_IDs are associated with the Fire_Hazard_ID corresponding to the Building_ID you selected? Did I misunderstand or have your requirements changed?

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