Forum Discussion
Map visual Filtering - Tooltip
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.
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.
Unfortunately this has the same result as the solution provided by Kedar_Pande
