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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.