Tooltip using custom measure returning blank rows when event ended

[paynec]

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.

[v-stephen-msft · Accepted solution]

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.