Tooltips for Matrix gantt chart with MULTIPLE start and end times

[ettt]

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:

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:

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:

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.

[Anonymous · Accepted solution]

Hi  @ettt ,

You can create an Index for the entire table:

Add Column – Index Column – From 1.

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

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.