Forum Discussion

GettingThere's avatar
GettingThere
Frequent Visitor
4 years ago
Solved

Dynamic coordinates depending on hierarchy

Hi,  I have a hierarchy of geography containing 4 levels. My report has drill-down through each of these geographies and that works well on chart visuals. However, I want to include a custom tooltip...
Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    4 years ago

    GettingThere  This might not be the most efficient way, but it works: 

     

    I stacked all locations in one column so there's only one lat column and one long column, (did this in Power Query).

     

    Then created a measure to filter the new Locations stacked table:

     

    Filter_HASONEVALUE =

    VAR _LocationID =
    SWITCH(
    TRUE,
    HASONEVALUE(Dim_Destination[CentreName]),"C" & SELECTEDVALUE(Dim_Destination[CentreID]),
    HASONEVALUE(Dim_Destination[TradeZoneName]),"TZ" & SELECTEDVALUE(Dim_Destination[SuperTradeZoneID]),
    HASONEVALUE(Dim_Destination[SuperTradeZoneName]),"STZ" & SELECTEDVALUE(Dim_Destination[SuperTradeZoneID]),
    HASONEVALUE(Dim_Destination[StandardRegion]),"SR " & SELECTEDVALUE(Dim_Destination[StandardRegion])
    )
    VAR _FilterLocations =
    FILTER(Locations,
    Locations[SourceID] = _LocationID
    )
    VAR _Result = COUNTROWS(_FilterLocations)
    RETURN _Result
     
    See file below signature.
GettingThere's avatar
GettingThere
Frequent Visitor
4 years ago

AllisonKennedy thank you for your suggestion, but it would be misleading for the report viewer. Ideally the coordinates would interact with the hierarchy. 

 

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    4 years ago

    GettingThere  This might not be the most efficient way, but it works: 

     

    I stacked all locations in one column so there's only one lat column and one long column, (did this in Power Query).

     

    Then created a measure to filter the new Locations stacked table:

     

    Filter_HASONEVALUE =

    VAR _LocationID =
    SWITCH(
    TRUE,
    HASONEVALUE(Dim_Destination[CentreName]),"C" & SELECTEDVALUE(Dim_Destination[CentreID]),
    HASONEVALUE(Dim_Destination[TradeZoneName]),"TZ" & SELECTEDVALUE(Dim_Destination[SuperTradeZoneID]),
    HASONEVALUE(Dim_Destination[SuperTradeZoneName]),"STZ" & SELECTEDVALUE(Dim_Destination[SuperTradeZoneID]),
    HASONEVALUE(Dim_Destination[StandardRegion]),"SR " & SELECTEDVALUE(Dim_Destination[StandardRegion])
    )
    VAR _FilterLocations =
    FILTER(Locations,
    Locations[SourceID] = _LocationID
    )
    VAR _Result = COUNTROWS(_FilterLocations)
    RETURN _Result
     
    See file below signature.