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

[charonT]

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.

[v-tangjie-msft]

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]

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.

[v-tangjie-msft]

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. 

[charonT]

Apologies if my previous explanation was unclear. Let me provide further details.

I have two tables: 'Population' and 'Score.'

Table 1: Population

Table 2: Score

I've created a metrics visual with hierarchy levels to display the population.

Figure 1: Metrics in Population

I aim to add a tooltip with content from 'Score.' When hovering over a city, it should display data corresponding to that city (labelled as level 1). Similarly, hovering over a county should reveal data specific to that county (labelled as level 2), and so forth for country (level 3).

For example, when hovering over City A, categorized under County A and Country A, this is the tooltip I expect to see:

Figure 2. Tooltip for City A

Likewise, when hovering over County A, the expected tooltip is:

Figure 3. Tooltip for County A

This DAX represents the logic I have in mind, but I'm unsure how to implement it practically. Thank you for your assistance in advance.

= if(DISTINCTCOUNT('Population'[City])=1,filter('Score',[level]=1),if(DISTINCTCOUNT('Population'[City])>1&&DISTINCTCOUNT('Population'[County])=1,FILTER('Score',[level]=2),if(DISTINCTCOUNT('Population'[County])>1,filter('Score',[level]=3))))