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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
v-stephen-msft
Community Support
Community Support

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
v-stephen-msft
Community Support
Community Support

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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