Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
stuhasic
New Member

Populating Tooltip Text from something like a VLOOKUP

Hi all, I'm very new to Power BI having first launched it today, but I feel I've come a long way 🙂  I've done a search here but can't really find what I'm looking for, so any assistance is greatly appreciated.

 

Some background - I have a program that monitors a large number (thousands) of devices and reports back those devices that fail to respond. Identifying them by location (name and coordinates), what they are and their address.  It spits out the information into three CSV files which in turn get sent to OneDrive for Business and have been added to a Power BI visualisation that looks like this:

 

pingermapdashboard.jpg

 

Clicking on the segments of the pie chart automatically updates all the numbers and shows subsets of the full data in the table shown at bottom.  The map also updates to show the location of the outages.  The script runs every hour and Power BI refreshes it when its ready to, on its hourly schedule.

 

Here's the bit that I am missing.  When I hover over any of the addresses at the bottom, all that shows is the IP address of the device.  What I'd like it to show is a text field which indicates when that specific outage was first detected via the script.  One of the CSVs the script produces is a two column table with all of the IP addesses that failed to repond and the Date-Time it was first reported.  That CSV has been added to Power BI, but I can't work out how to use the data it contains to display the matching IP's date/time.

 

So what I need is when the table above generates, it needs to do like an Excel VLOOKUP to match the IP on the table with the corresponding IP in the other CSV and populate its Tooltip text with the contents of the adjoining cell.

 

Hope that all makes sense and is doable.  I'm super impressed with what I was able to achieve in such a short amount of time with PBI today.  Thanks in anticipation.

 

4 REPLIES 4
Phil_Seamark
Employee
Employee

Hi @stuhasic

 

If you can create a 1 to many relationship between the two tables then you can achieve what you are after.  The DAX functions are RELATED or RELATEDTABLE (depending on which way you are looking).

 

If you post a small sample of dummy data for the two tables we can suggest a forumla for a calculated measure that you can drag to the Tooltip.

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thanks @Phil_Seamark

 

I decided I'd better block out the IP addresses in the above screenshot.  Here are the two files I need data associated from.  The IP addresses have been anonymised, so they probably won't match up, but you get the idea.  All the address in the top file are in Column A of the bottom file.

 

IPoutages.csv

4de1SwX

 

Failures.csv

x92AVSP

So it's the matching IP's contents of column B I want in the tooltip text of the chart in my OP.

 

Thanks.

 

Sorry, this whole thread disappeared yesterday but now it's back. Just bumping it. Hope that's OK.

Hi @stuhasic,

Firstly, the table visual in Power BI Desktop doesn’t  provide Tooltips feature like other charts(for example, stacked column chart). You are not able to show text field as you describe when you hover over any of the addresses in table visual.
1.PNG

Secondly, as a workaround for this issue, you can create extra column to present what you want to show, add the extra column in the table visual and adjust the column width so it just shows “…”(the Auto-size column width and Word wrap should be turned off for the table visual). This way, when you hover over the “…”, text us displayed.

Thirdly, to create the extra column, you would need to create a new table using the following DAX. Then create relationship using Device IP field in the new table and Telstra WAN field in IPoutages table.

NewTable = SUMMARIZE(Failures,Failures[Device IP],"failure time", MIN(Failures[Failed Since]))

After that, create the extra column in IPoutages table using DAX below, then add the column to your table visual.

Column = RELATED('NewTable'[failure time])

2.PNG



Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.