cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sjdb
Regular Visitor

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.

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
amitchandak
Super User
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

 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

@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
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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!

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!