Forum Discussion

charonT's avatar
charonT
Helper I
2 years ago

Need Help: displaying tooltip table on a table with hierarchy levels

I have a matric visual with three levels of hierarchy: country, county, and city, structured as follows:

 

+ Country A

    + County A

        city A     Population 

        city B     Population

        city C     Population

 

I would like to display a tooltip that shows data corresponding to the selected hierarchy level. The current data structure is as follows:

 

Country A     Empty         Empty     Male        Income

Country A     Empty         Empty     Female    Income

Country A     County A    Empty     Male        Income

Country A    County A     Empty     Female    Income

Country A    County A      City A     Male        Income

Country A    County A      City A     Female     Income    

 

When hovering over city-level data, I want to see a table including the data from the last two rows. If I hover over the County A cell, I want to see a table including the data from the middle two rows.

It needs to be a table in the tooltip for specific reasons. While this example does not reflect my actual data, I am only able to achieve this using a chart in the tooltip.

Is this possible? My idea is to write a DAX measure to detect the scenario and apply it as a filter in the tooltip table.

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi charonT ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2) We can copy a table in power query. [Table(3)]

    (3) We can create a measure. 

    Measure 2 = var a=COUNTROWS(FILTER('Table (3)',[City ]=MAX('Table'[City ])&&[City ]<>BLANK()))
var b=COUNTROWS(FILTER('Table (3)',[County]=MAX('Table'[County])&&[County]<>BLANK()&&[City ]=BLANK()))
var c=COUNTROWS(FILTER('Table (3)',[Country]=MAX('Table'[Country])&&[Country]<>BLANK()&&[County]=BLANK()))
return IF(a>0,1,IF(b>0&&MAX('Table'[City ])=BLANK(),1,IF(c>0&&MAX('Table'[County])=BLANK()&&MAX('Table'[City ])=BLANK(),1,0)))

    Place [Measure2=1] on the filter for visual objects on the tooltip page.

     

    (4) Then the result is as follows.

     

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

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

    • charonT's avatar
      charonT
      Helper I
      2 years ago

      Hi Neeko,

      Thank you for your quick reply. Sorry I did not explain what I expected accurately and I am not able to upload a file in this forum.

       

      Based on your sample data, I have changed the format of 'Table' to match my real data format

      And this is the metrics (Sorry I did say its a table but its not) in my data

      What I expect to show on the tooltip in the county level is the the index 3 and 4 in Table 3

       

      And to show Index 1 and 2 at country level.

       

      City level shows exactly what I want

      Thank you in advance.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi charonT ,

         

        We can copy a table in power query.

        We can create a measure.

        Flag = 
var _a=ISFILTERED('Table'[Country])&ISFILTERED('Table'[County])&ISFILTERED('Table'[City ])
var _id=SELECTEDVALUE('Table 2'[Index])
var cur_city=SELECTEDVALUE('Table'[City ])
var _b=SWITCH(TRUE(),
ISFILTERED('Table'[Country])=TRUE()&&ISFILTERED('Table'[County])=FALSE()&&ISFILTERED('Table'[City ])=FALSE(),IF(_id in {1,2},1),
ISFILTERED('Table'[Country])=TRUE()&&ISFILTERED('Table'[County])=TRUE()&&ISFILTERED('Table'[City ])=FALSE(),IF(_id in {3,4},1),
ISFILTERED('Table'[Country])=TRUE()&&ISFILTERED('Table'[County])=TRUE()&&ISFILTERED('Table'[City ])=TRUE(),IF(SELECTEDVALUE('Table 2'[City ])=cur_city,1))
RETURN
_b

        On the tooltip page, place the field [Table2] on the visual object and [Flag=1] on the visual object filter.

        If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

         

        Best Regards,

        Neeko Tang

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