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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
paynec
Regular Visitor

Tooltip using custom measure returning blank rows when event ended

Hi, 

 

I have been working building a custom tooltip to show details of events which were active when a user hovers over a data point on my visualisation. The visualisation is a line chart for average time taken to complete an action per month, with a moving average calculation.

 

I have beeen attempting to use measures to pull through values from columns within a fact table called "Events" as long as the date of the data point hovered over is during between Event[Start date] and Event[End date]. This is then displayed on the tooltip, for example, January 2022 should show "LOS Whitefield to Bury operational" under Description and "Operational change" under Category. 

 

However, it seems that the measures are pulling values for all event entries and 'whiting out' all values that are not within the date ranage as per the data point being hovered over. The image below shows what I mean, the "LOS Whitefield to Bury operational" is visible under 7 'whited out' event entries which correlate to events that have finished prior to January 2022.

 

The measure for Description is as follows:

 

Event desc tooltip =

VAR _CurrentDate = MAX(dimMOMA[Date])
VAR _Events =
    CONCATENATEX(
        'Events (Line)',
        IF(
            _CurrentDate IN CALENDAR('Events (Line)'[Event start date],'Events (Line)'[Event end date]),
            'Events (Line)'[Description]
        ),
        UNICHAR(10)
    )
RETURN
    _Events
 
Is there a way that I can still pull through this information from the Events fact table for the relevant time period without all previous event entries being visible (yet whited out) within the table?
 
I cannot share the pbix file as it contains data sensitive to my organisation.

 

paynec_1-1680709975486.png

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @paynec ,

 

It seems like the measure you are using is pulling all the events that are in the date range of the current date. To only show the event that is active during the date range of the data point being hovered over, you can modify the measure to filter the events based on the date range of the data point. You may try the following measure:

Event desc tooltip =
VAR _CurrentDate = MAX(dimMOMA[Date])
VAR _Events =
    CALCULATE(
        CONCATENATEX(
            'Events (Line)',
            'Events (Line)'[Description],
            UNICHAR(10)
        ),
        FILTER(
            'Events (Line)',
            _CurrentDate >= 'Events (Line)'[Event start date] &&
            _CurrentDate <= 'Events (Line)'[Event end date]
        )
    )
RETURN
    _Events

This measure will only concatenate the descriptions of the events that are active during the date range of the data point being hovered over. Let me know if this helps or if you have any further questions.

 

                                                                                                                                                         

Best Regards,

Stephen Tao

 

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

View solution in original post

1 REPLY 1
Anonymous
Not applicable

Hi @paynec ,

 

It seems like the measure you are using is pulling all the events that are in the date range of the current date. To only show the event that is active during the date range of the data point being hovered over, you can modify the measure to filter the events based on the date range of the data point. You may try the following measure:

Event desc tooltip =
VAR _CurrentDate = MAX(dimMOMA[Date])
VAR _Events =
    CALCULATE(
        CONCATENATEX(
            'Events (Line)',
            'Events (Line)'[Description],
            UNICHAR(10)
        ),
        FILTER(
            'Events (Line)',
            _CurrentDate >= 'Events (Line)'[Event start date] &&
            _CurrentDate <= 'Events (Line)'[Event end date]
        )
    )
RETURN
    _Events

This measure will only concatenate the descriptions of the events that are active during the date range of the data point being hovered over. Let me know if this helps or if you have any further questions.

 

                                                                                                                                                         

Best Regards,

Stephen Tao

 

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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