Forum Discussion
How to create ToolTip table showing all Text values within a given date range
If I have a DataTable with columns [Comments], [Start Date], [End Date] and [Value], and I plot Value on a daily basis as a Stacked Column Chart using a monthly slicer, how can I create a ToolTip where when I hover over a particular day's Column it will show all Comments and Values where the particular day falls on or between the Start Date and End Date? [Comments] is Text type information, and [Value] is Decimal type information. I have a separate Calendar table with dates etc. in it that currently has a Relationship between Calendar[Date] and DataTable[Start Date].
E.g. If I hover over the Stacked Column Chart Value item for 15 June 2020, and two of the rows in DataTable have
Start Date = 12 June 2020 and End Date = 15 June 2020
Start Date = 13 June 2020 and End Date = 23 June 2020
I'd like to show the Value and Comments in a Table Visualisation in a ToolTip with both of those rows. And obviously if more than these two meet the [Start Date] <= Hovered Date >= [End Date] criteria, I'd like all of them shown too.
Hi sjdb,
You can try to use CONCATENATEX function, it can concatenate a list of field values to single text string:
Measure = CONCATENATEX ( CALCULATETABLE ( VALUES ( Table[Text] ), ALLSELECTED ( Table ), VALUES ( Table[Category] ) ), [Text], "," )Regards,
Xiaoxin Sheng
4 Replies
- amitchandak
Super User
sjdb , create a measure like that which takes the selected value and calculate this date range and filter the data. Use this measure in the tooltip page and try.
https://docs.microsoft.com/en-us/power-bi/desktop-tooltips
amitchandak , sorry I found your reply a little confusing. Are you saying I should use the CALCULATE function to create the measure for the ToolTip? What exactly would the code for the CALCULATE dax look like, as I have tried using this with no success so far.
What would the Expression be for the first portion of the CALCULATE function? (normally I would use SUM but the DataTable[Comments] column is of Text type data...)
Thanks!
Hi sjdb,
You can try to use CONCATENATEX function, it can concatenate a list of field values to single text string:
Measure = CONCATENATEX ( CALCULATETABLE ( VALUES ( Table[Text] ), ALLSELECTED ( Table ), VALUES ( Table[Category] ) ), [Text], "," )Regards,
Xiaoxin Sheng
