Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
ettt
Frequent Visitor

Tooltips for Matrix gantt chart with MULTIPLE start and end times

Hi everyone,

 

I've been searching the internet and the community for a few weeks now andI still haven't been able to find an answer for this.

 

I'm currently attempting to make a gantt chart with the matrix visual for promotions through the year. I currently have a table set up like this:

 

ettt_0-1713153508389.png

My matrix is aggregated by Store, Brand, and Product. However, as seen by the table, there are some promotions that have the exact same information, just different dates (thus, should appear on the same row in the matrix). I have tried to do this using the following code:

ettt_2-1713154752885.png

 

 

GanttValue = 
SUMX(
    'Date Table',
    VAR CurrentDate = 'Date Table'[Date]
    RETURN
        IF(
            // Check if the current date falls within any promo period
            COUNTROWS(
                FILTER(
                    'Sheet1',
                    CurrentDate >= 'Sheet1'[Promotion Start] && 
                    CurrentDate <= 'Sheet1'[Promotion End]
                )
            ) > 0,
            1, // If yes, return 1
            0  // If no, return 0
        )
)

 

 

However, as you can see from the screenshot, it doesnt pull values of 0 or 1, rather values such as 2 and 7. With formatting technically this does not impact anything with the visual - however, I am attempting to make custom tooltips to show the promotional details.

 

I have tried using the tooltip page "values" however, this will only show values for the first promotion and on empty cells.

 

So I tried making a customer tool tip measure with the code below:

 

 

Tooltip = 
VAR GanttValue = [GanttValue] -- Get the Gantt value for the current cell

RETURN
    IF(
        GanttValue = 0,  -- Check if the Gantt value is 0
        BLANK(),  -- If yes, return blank
        // Calculate the Promo information
        CALCULATE(
            CONCATENATEX(
                FILTER(
                    'Sheet1',
                    Sheet1[Promotion Number] = SELECTEDVALUE(Sheet1[Promotion Number])
                ),
                "Promo ID: " & Sheet1[Promotion Number] & ", " &
                "Promo Start: " & FORMAT(Sheet1[Promotion Start], "MMM d") & ", " &
                "Promo End: " & FORMAT(Sheet1[Promotion End], "MMM d") & ", " &
                ", "
            ),
            Sheet1[Promotion Number] = SELECTEDVALUE(Sheet1[Promotion Number])
        )
    )

 

 


However, this code will only show promotional information if there is only ONE promotion in that row. If there are multiple, it comes up empty:

ettt_3-1713155323939.png

ettt_4-1713155365025.png

 

How can I ensure that when I hover my tool tips show the correct promotional information for multiple promotions on the same row? 

I appreciate any and all help that anyone can lend. I am still a beginner with Power BI so all help is welcome.

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

Hi  @ettt ,

 

You can create an Index for the entire table:

Add Column – Index Column – From 1.

vyangliumsft_0-1713251429690.png

First create a measure1 grouped by [Index] to show the information about each promotional

Measure1=          
 CONCATENATEX(
                FILTER(
                    'Sheet1',
                    Sheet1[Index] = SELECTEDVALUE(Sheet1[Index])
                ),
                "Promo ID: " & Sheet1[Promotion Number] & ", " &
                "Promo Start: " & FORMAT(Sheet1[Promotion Start], "MMM d") & ", " &
                "Promo End: " & FORMAT(Sheet1[Promotion End], "MMM d") & ", " &
                ", "
            ), “-”
        )

Then create measure2 to group and splice [Measure1] according to [Promotion Number], [Store], [Brand].

Measure 2 =
CONCATENATEX(
    FILTER(ALL('Table'),
    'Sheet'[Promotion Number]=MAX('Table'[PromotionNumber])&&'Table'[Store]=MAX('Table'[Store])&&'Table'[Brand]=MAX('Table'[Brand])),[Measure],"/")

vyangliumsft_1-1713251429692.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-yangliu-msft
Community Support
Community Support

Hi  @ettt ,

 

You can create an Index for the entire table:

Add Column – Index Column – From 1.

vyangliumsft_0-1713251429690.png

First create a measure1 grouped by [Index] to show the information about each promotional

Measure1=          
 CONCATENATEX(
                FILTER(
                    'Sheet1',
                    Sheet1[Index] = SELECTEDVALUE(Sheet1[Index])
                ),
                "Promo ID: " & Sheet1[Promotion Number] & ", " &
                "Promo Start: " & FORMAT(Sheet1[Promotion Start], "MMM d") & ", " &
                "Promo End: " & FORMAT(Sheet1[Promotion End], "MMM d") & ", " &
                ", "
            ), “-”
        )

Then create measure2 to group and splice [Measure1] according to [Promotion Number], [Store], [Brand].

Measure 2 =
CONCATENATEX(
    FILTER(ALL('Table'),
    'Sheet'[Promotion Number]=MAX('Table'[PromotionNumber])&&'Table'[Store]=MAX('Table'[Store])&&'Table'[Brand]=MAX('Table'[Brand])),[Measure],"/")

vyangliumsft_1-1713251429692.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.