Filtering Map Events by Active Date Range Using Start and End Dates in Power BI

[GabrielLyrio]

I'm using a scatter chart to pinpoint events in a map using X and Y Axis, but the probem i'm running into is that these events have two important dates, the initial and ending dates, wich I'd like to use in a Slicer where I could hava the graph only showing events wich:
Event Starting Date > Initial Slicer date
Event Finishing Date > Final Slicer date

Chatgpt came up with creating a separate table:

Calendario =

CALENDAR (

    MIN('Formulario REPARACAO'[Data do Evento]),

-- (Data do Evento = Event Starting Date)

    MAXX('Formulario REPARACAO', COALESCE('Formulario REPARACAO'[DataInativacao], TODAY()))

-- (DataInativacao = Event Finishing Date)

)

And then using the tables value to create a calculated column that was supposedly to have its Max and Min values based on the values selected on the slicer and based on that, attribut binary values to each event that tell if it is happening or not:

EventoVisivel =
VAR DataMin = MIN('Calendario'[Date])
VAR DataMax = MAX('Calendario'[Date])
RETURN
IF (
'Formulario REPARACAO'[Data do Evento] <= DataMax &&
(
ISBLANK('Formulario REPARACAO'[DataInativacao]) ||
'Formulario REPARACAO'[DataInativacao] > DataMin
),
1,
0
)

My problem is, that according to chatgpt Calculated columns can't calculate based on a slicers value and all my events just end up being equal to 1, I also tried using a Measure but it didn't work and doesn't makes much sense since every event needs it's own value. I'd like to know any ways to workaround this isssue or if there's any other easier way of doing what I'm trying.

The relations are:
Formulario REPARACAO (Concatenar) * < 1 Localizações (concatenar)
Formulario REPARACAO (DataInativacao) * < 1  Calendario(Date)
Formulario REPARACAO (Data do Evento) * < 1 Calendario(Date)

The sheets being used are in the following folder:
https://drive.google.com/drive/folders/1DzBA9VpZO6iH7v6llOWDXirR1PaAV49B?usp=sharing

[v-kpoloju-msft]

Hi @GabrielLyrio,
Thank you for reaching out to the Microsoft fabric community forum.

I understand that you are trying to filter a scatter chart on a map in Power BI based on a date range selected via a slicer, showing only events active between a start and end date. You're correct that calculated columns cannot dynamically respond to slicer input, which results in all events being visible regardless of the selected dates.

To address this, we recommend using a DAX measure to determine if each event is active within the selected date range. This measure can then be applied as a visual-level filter on your scatter chart.

Keep your calendar table as you have it:

Calendario =

CALENDAR (

    MIN('Formulario REPARACAO'[Data do Evento]),

    MAXX('Formulario REPARACAO', COALESCE('Formulario REPARACAO'[DataInativacao], TODAY()))

)

Use Calendario [Date] in a slicer as a Between date range.
Create the following measure:

EventoAtivo =

VAR DataMin = MIN('Calendario'[Date])

VAR DataMax = MAX('Calendario'[Date])

RETURN

IF (

    'Formulario REPARACAO'[Data do Evento] <= DataMax &&

    (

        ISBLANK('Formulario REPARACAO'[DataInativacao]) ||

        'Formulario REPARACAO'[DataInativacao] >= DataMin

    ),

    1,

    0

)

Apply this measure as a visual-level filter on your scatter chart. In the Filters pane, drag in EventoAtivo. Set the filter to show only where EventoAtivo = 1.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @GabrielLyrio,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[GabrielLyrio]

Unfortunately this solution doens't solve my issue, since I need a single value for each event and a calculated measure can't do that, even so, I tried to apply the above but, like I thought, i didn't work and gave me the following error code:
"It is not possible to determine a single value for the ''Event Date'' column in the ''FORMULARIO REPARACAO'' table. This can happen when a measure formula refers to a column containing many values without specifying an aggregation such as min, max, count or sum to obtain a single result."

I'm wondering if there is even away of doint this inside the scatte chart visual or if thre is any other free visual that would fit better 

[v-kpoloju-msft · Accepted solution]

Hi @GabrielLyrio,

Thank you for the follow-up and for sharing the specific error you're encountering:

"It is not possible to determine a single value for the ''Event Date'' column..."

This error typically arises when a measure is employed in a context where Power BI expects a single row-level value, but the expression returns multiple rows without an aggregation function like MIN, MAX, etc. Measures are context-sensitive and designed to aggregate, so they won't return one value per row as a calculated column does.

Regarding the Scatter Chart: The Scatter Chart visual requires well-defined numeric X and Y values for each data point. If you aim to plot events based on specific dates (or a single date per event), a measure won't work directly unless it has been properly aggregated. For example:

Event Date (Max) = MAX('FORMULARIO REPARACAO'[Event Date])

However, this approach is only viable if aggregating the data is acceptable. If row-level event data is essential, I suggest the following:

Utilize a Calculated Column (if your model supports it) to extract or transform the event date for each row, allowing you to have a single, fixed value. For example:

Single Event Date = 'FORMULARIO REPARACAO'[Event Date]

In this case, the scatter chart can use this field directly. However, if you're aiming to plot start and end dates for events and represent durations or intervals, a scatter chart may not be the most suitable visual.

Consider these alternative visuals:

• Gantt Chart (custom visual) – Ideal for plotting event durations using start and end dates.
• Timeline or Line Chart – Useful for displaying events over time and highlighting values.
• Matrix/Table visual – Effective for listing events with calculated measures, especially if interactivity is required. You can explore custom visuals via the Power BI Visuals Marketplace.

Please provide more details about your expected outcome (e.g., are you trying to show start vs. end dates per event? Duration? Categorized scatter?), and I’d be happy to tailor a solution or share a PBIX sample if needed.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @GabrielLyrio,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @GabrielLyrio,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-kpoloju-msft]

Hi @GabrielLyrio,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.