Forum Discussion

Foxxon28's avatar
Foxxon28
Helper I
1 year ago
Solved

Map visual Filtering - Tooltip

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 Lo...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

Foxxon28's avatar
Foxxon28
Helper I
1 year ago

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's avatar
Kedar_Pande
Super User
1 year ago

What is expected when no filters are applied?

  • Foxxon28's avatar
    Foxxon28
    Helper I
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        Foxxon28
        Helper I
        1 year ago

        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.