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 which shows the location on a map using X & Y coordinates when hovering over a bar. To achieve this I need the co-ordinates to dynamically change depending upon the hierarchy within the chart. 

 

I have written the below measure which correctly returns the X_Coord and works if I add the measure as a column to a matrix, however it retuns blank when I add the measure to Longitude in the Map visual. I have the equivalent measure for latitude too.

 
XCoord=
SWITCH(
TRUE,
ISINSCOPE(Dim_Destination[CentreName]),MAX(Dim_Destination[X_WGS84]),
ISINSCOPE(Dim_Destination[TradeZoneName]),MAX(Dim_Destination[TZ_X_WGS84]),
ISINSCOPE(Dim_Destination[SuperTradeZoneName]),MAX(Dim_Destination[STZ_X_WGS84]),
ISINSCOPE(Dim_Destination[StandardRegion]),MAX(Dim_Destination[SR_X_WGS84]),
1
)
 
I have spent a long time looking for suggested solutions but I'm stuck. Is it possible to achieve this? 
 
Hopefully this link to my pbix works! https://we.tl/t-PldXRYhmVd
 
Thanks in advance for your help!
 
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.

3 Replies

  • 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.