Forum Discussion
Filtering Map Events by Active Date Range Using Start and End Dates in Power BI
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.
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.
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.
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 betterHi 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.
