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.
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.