Forum Discussion
Competitive and Market Chart Annotation
Hi Perplexed , Thank you for reaching out to the Microsoft Community Forum.
You should set up your data model to avoid ambiguity. Create a DimQuarter table with unique quarters and link it to both your Revenue and Events tables via the Quarter column (1-to-many). Skip direct Market or Vendor relationships between tables to prevent path errors. Instead, make two disconnected tables, Slicer_Market and Slicer_Vendor, with unique Market and Vendor values for your slicers. This clean setup ensures no ambiguity and supports the new Text Slicer’s multi-select feature from January 2025.
Create DAX measures to handle dynamic filtering and clean event tooltips. These measures should be able to make your chart and tooltips update instantly with slicer selections and CONCATENATEX ensures events display fully without Power BI’s default First/Last/Count summarization.
Example:
FilteredRevenue =
CALCULATE(
SUM(Revenue[Revenue]),
FILTER(
Revenue,
Revenue[Market] IN VALUES(Slicer_Market[Market]) &&
Revenue[Vendor] IN VALUES(Slicer_Vendor[Vendor])
)
)
EventTooltip =
CONCATENATEX(
FILTER(
Events,
Events[Quarter] = SELECTEDVALUE(DimQuarter[Quarter]) &&
Events[Market] IN VALUES(Slicer_Market[Market]) &&
Events[Vendor] IN VALUES(Slicer_Vendor[Vendor])
),
Events[Event],
", "
)
For the visuals, create a line chart with DimQuarter[Quarter] on the X-axis and FilteredRevenue on the Y-axis. Overlay a scatter plot for events: use DimQuarter[Quarter] for the X-axis, a constant Y-value (e.g., 10% above max revenue for visibility) and EventTooltip for the tooltip. If you want sharper annotations, use the Deneb custom visual with Vega-Lite spec. Map the Events table to Deneb, using Quarter and EventTooltip. This setup ensures your annotations update dynamically with slicers and show exact event names. To keep it snappy with larger datasets, pre-aggregate data in Power Query and index Quarter columns.
Example Vega-Lite spec:
{
"data": {"name": "dataset"},
"mark": {"type": "point", "filled": true, "size": 100},
"encoding": {
"x": {"field": "Quarter", "type": "ordinal", "axis": {"title": "Quarter"}},
"y": {"value": 0},
"tooltip": [{"field": "EventTooltip", "type": "nominal", "title": "Events"}]
}
}
If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
Hi Perplexed ,
Yes, you can build an annotated timeline chart in Power BI that updates based on slicers for Market and Vendor. Here's how to approach it:
1. Model Setup
- Make sure both tables (Revenue Data and Events) are connected via Quarter, Market, and Vendor.
- You might need to create a composite key or use a bridge table if the relationships are many-to-many.
2. Create the Line Chart
- Use Quarter on the X-axis.
- Use SUM(Revenue) or similar on the Y-axis.
- Add slicers for Market and Vendor.
3. Add Annotations
- Use a scatter plot or custom visual (like Deneb or Charticulator) to overlay event markers.
- Plot the Event table with:
- X-axis: Quarter
- Y-axis: a constant (e.g., 0 or a dummy value)
- Tooltip: Event
This way, when you filter by Market or Vendor, both the line chart and the event markers will update accordingly.
Let me know if you want a sample PBIX or help with the relationships.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI
burakkaragoz Thank you for the response. I had tried a similar approach as you mention above. Here are the challenges I am facing in trying this.
1) I have "Unique Vendor", "Unique Market", and "Unique Quarter" dimensions/bridge tables to allow for the alignments between the data tables to allow a 1 to many mapping to the market data. When I try to map the timeline table to more than one dimension I get an ambigious path error.
2) the tool tip or data label options force a summerisation [first, last or count]. so on the occations where I have tried things and got the data to align and show in the chart I just get the repeated sumerisation across the time periods.
Thanks,
Jason.
Hi Perplexed , Thank you for reaching out to the Microsoft Community Forum.
You should set up your data model to avoid ambiguity. Create a DimQuarter table with unique quarters and link it to both your Revenue and Events tables via the Quarter column (1-to-many). Skip direct Market or Vendor relationships between tables to prevent path errors. Instead, make two disconnected tables, Slicer_Market and Slicer_Vendor, with unique Market and Vendor values for your slicers. This clean setup ensures no ambiguity and supports the new Text Slicer’s multi-select feature from January 2025.
Create DAX measures to handle dynamic filtering and clean event tooltips. These measures should be able to make your chart and tooltips update instantly with slicer selections and CONCATENATEX ensures events display fully without Power BI’s default First/Last/Count summarization.
Example:
FilteredRevenue =
CALCULATE(
SUM(Revenue[Revenue]),
FILTER(
Revenue,
Revenue[Market] IN VALUES(Slicer_Market[Market]) &&
Revenue[Vendor] IN VALUES(Slicer_Vendor[Vendor])
)
)
EventTooltip =
CONCATENATEX(
FILTER(
Events,
Events[Quarter] = SELECTEDVALUE(DimQuarter[Quarter]) &&
Events[Market] IN VALUES(Slicer_Market[Market]) &&
Events[Vendor] IN VALUES(Slicer_Vendor[Vendor])
),
Events[Event],
", "
)
For the visuals, create a line chart with DimQuarter[Quarter] on the X-axis and FilteredRevenue on the Y-axis. Overlay a scatter plot for events: use DimQuarter[Quarter] for the X-axis, a constant Y-value (e.g., 10% above max revenue for visibility) and EventTooltip for the tooltip. If you want sharper annotations, use the Deneb custom visual with Vega-Lite spec. Map the Events table to Deneb, using Quarter and EventTooltip. This setup ensures your annotations update dynamically with slicers and show exact event names. To keep it snappy with larger datasets, pre-aggregate data in Power Query and index Quarter columns.
Example Vega-Lite spec:
{
"data": {"name": "dataset"},
"mark": {"type": "point", "filled": true, "size": 100},
"encoding": {
"x": {"field": "Quarter", "type": "ordinal", "axis": {"title": "Quarter"}},
"y": {"value": 0},
"tooltip": [{"field": "EventTooltip", "type": "nominal", "title": "Events"}]
}
}
If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.