How to create ToolTip table showing all Text values within a given date range

[sjdb]

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.

[amitchandak]

@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

[sjdb]

@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!

[v-shex-msft · Accepted solution]

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],
    ","
)

CONCATENATEX function 

Regards,

Xiaoxin Sheng

[Yoda_Fart]

This is awesome, but the code doesn't address the date requirenment. If the user wanted to see the values for a specific date, how would they incorporate that requirment into the piece of code you provided? Thanks!