Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
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.
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.
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.
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).
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
86 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |