Gantt Chart with SVG objects in a measure and matrix visual

[Patricio479]

Hello, dear PowerBi experts!

I saw a post in one of my social networks showing a nice collapsible Gantt Chart that can be done  using SVG objects in a measure and the matrix visual (at least this was the claim from the owner of the post). I was surprised by the final result which includes the visualization of a baseline and current bars, milestones (diamond shape), a graphic visualization of the progress (blue color in the bar) and summary activity (or parent activity) that can be collapsed/expanded. Can somebody provide me with a summary of the steps to get such result so I can investigate and recreate someting similar? Could you help me indicating a guide to understand SVG and how to use it in this case?

I have to say that I don't know of any free version "ready to use" app for Gantt Charts in the PowerBi store that includes such capabilities, so I am amazed with the result of below example and what you can do using the existing build-in tools in Power Bi.

Thanks in advance for your comments!

Patricio.

[ITManuel · Accepted solution]

Hi @Patricio479 ,

I got inspired by exactly the same post and spent a bit of time in trying to rebuild something similiar. I tried various available Gantt chart visualization available for Power BI before, but wasn't happy at all, so I decided to built something basing on a standard matrix visual using SVG objects since it has been done by several people before.

Some informations:

• I'm using data from the Project web app ( Microsoft Project ) via an ODataFeed connector
• The data contains information in relation to parent - child hierarchies of the tasks, so I implemented the parent child solution as per the SQL BI pattern available: https://www.daxpatterns.com/parent-child-hierarchies/
• This will provide the possibility to expand and collapse on tasks ( summary tasks etc. ) 
• The bar visualization has been created via SVG objects. The visualization part is nothing else than a column of the matrix visual in which via a measure SVG objects are created. There are several tutorials on youtube how to do this, also ChatGPT can be very helpful in this matter. 
• Project summary tasks, summary tasks, tasks, milestones etc. appear differently in order to be able to distinguish between them
• The maximum width of SVG objects in the matrix is 512px. This is a limitation which has to be considered, the bars of the individual lines are scaled to the 512px maximum width. --> 512px is the longest bar in the current selection, any other bar is scaled by the measure accodingly.
• I also created a time bar which shows Years and months and placed it in the column header of the column which visulalizes the SVG's in the matrix. This is nothing else than the standard Power BI table in which via a dedicated measure the time bar is created also via a SVG. If positioned correctly in the column header, it appears as it would be an integral part of the Gantt chart. Infact it is a separate visualization.

The result is the following:

The code for the bars in the above example is the following. SVG codes tend to get quite long, but in the end it is not very complex but more repetitive once the concept has been understood.

GanttSVG = 
VAR _MaxDuration =
    DATEDIFF (
        CALCULATE ( MIN ( Tasks[TaskStartDate] ), ALLSELECTED ( TasksHeader ) ),
        CALCULATE ( MAX ( Tasks[TaskFinishDate] ), ALLSELECTED ( TasksHeader ) ),
        DAY
    )
VAR _DurationP =
    DATEDIFF ( MIN ( Tasks[TaskStartDate] ), MAX ( Tasks[TaskFinishDate] ), DAY )
VAR _BarStart =
    DATEDIFF (
        CALCULATE ( MIN ( Tasks[TaskStartDate] ), ALLSELECTED ( TasksHeader ) ),
        MIN ( Tasks[TaskStartDate] ),
        DAY
    )
        * DIVIDE ( 512, _MaxDuration )
VAR _BarStartMS =
    SWITCH ( TRUE (), _BarStart < 4, 4, _BarStart > 508, 508, _BarStart )
VAR _TextStartMS =
    IF ( _BarStartMS < 450, _BarStartMS + 8, _BarStartMS - 38 )
VAR _BarWidth =
    _DurationP * DIVIDE ( 512, _MaxDuration )
VAR _StartDate =
    FORMAT ( MIN ( Tasks[TaskStartDate] ), "DD.MM.YY" )
VAR _TodayLine =
    DATEDIFF (
        CALCULATE ( MIN ( Tasks[TaskStartDate] ), ALLSELECTED ( TasksHeader ) ),
        TODAY (),
        DAY
    )
        * DIVIDE ( 512, _MaxDuration )
VAR _IsPST =
    SELECTEDVALUE ( TasksHeader[TaskIsProjectSummary] )
VAR _IsST =
    SELECTEDVALUE ( TasksHeader[TaskIsSummary] )
VAR _IsMS =
    SELECTEDVALUE ( TasksHeader[TaskIsMilestone] )
VAR _ColourTL =  "rgb(255,190,0)"
VAR _WidtTL = "0.5"
VAR _PST = "data&colon;image/svg+xml;utf8,<svg 
    width='512' height='16'
    xmlns='http://www.w3.org/2000/svg'  
    xmlns:xlink='http://www.w3.org/1999/xlink'>
        <rect x='" & _BarStart & "' y='4' width='" & _BarWidth & "' height='8'  fill='rgb(90, 90, 90)' stroke='rgb(255, 100, 30)' stroke-width='0.1' />
        <polygon points='" & _BarStart & ",4 " & _BarStart + 4 & ",4 " & _BarStart + 4 & ",12 " & _BarStart & ",16 ' fill='rgb(255, 100, 30)' />
        <polygon points='" & _BarStart + _BarWidth & ",4 " & _BarStart - 4 + _BarWidth & ",4 " & _BarStart - 4 + _BarWidth & ",12 " & _BarStart + _BarWidth & ",16 ' fill='rgb(255, 100, 30)' />
        <line x1='" & _TodayLine & "' y1='0' x2='" & _TodayLine & "' y2='16' style='stroke:" & _ColourTL & ";stroke-width:" & _WidtTL & "' />
    </svg>"
VAR _ST = "data&colon;image/svg+xml;utf8,<svg 
    width='512' height='16'
    xmlns='http://www.w3.org/2000/svg'  
    xmlns:xlink='http://www.w3.org/1999/xlink'>
        <rect x='" & _BarStart & "' y='5' width='" & _BarWidth & "' height='6'  fill='rgb(125, 125, 125)' stroke='rgb(190, 190, 190)' stroke-width='0.1' />
        <polygon points='" & _BarStart & ",5 " & _BarStart + 4 & ",5 " & _BarStart + 4 & ",11 " & _BarStart & ",15 ' fill='rgb(190, 190, 190)' />
        <polygon points='" & _BarStart + _BarWidth & ",5 " & _BarStart - 4 + _BarWidth & ",5 " & _BarStart - 4 + _BarWidth & ",11 " & _BarStart + _BarWidth & ",15 ' fill='rgb(190, 190, 190)' />
        <line x1='" & _TodayLine & "' y1='0' x2='" & _TodayLine & "' y2='16' style='stroke:" & _ColourTL & ";stroke-width:" & _WidtTL & "' />
    </svg>"
VAR _T = "data&colon;image/svg+xml;utf8,<svg 
    width='512' height='16'
    xmlns='http://www.w3.org/2000/svg'  
    xmlns:xlink='http://www.w3.org/1999/xlink'>
        <rect x='" & _BarStart & "' y='4' width='" & _BarWidth & "' height='8'  fill='rgb(190, 190, 190)' rx = '2.5'/>
        <line x1='" & _TodayLine & "' y1='0' x2='" & _TodayLine & "' y2='16' style='stroke:" & _ColourTL & ";stroke-width:" & _WidtTL & "' />
    </svg>"
VAR _MS = "data&colon;image/svg+xml;utf8,<svg 
    width='512' height='16'
    xmlns='http://www.w3.org/2000/svg'  
    xmlns:xlink='http://www.w3.org/1999/xlink'>
        <polygon points='" & _BarStartMS - 4 & ",8 " & _BarStartMS & ",4 " & _BarStartMS + 4 & ",8, " & _BarStartMS & ",12' fill='rgb(90, 90, 90)'/>
        <text x='" & _TextStartMS & "' y='11' font-family='Arial' font-size='8' fill='rgb(190, 190, 190)'>" & _StartDate & "</text>
        <line x1='" & _TodayLine & "' y1='0' x2='" & _TodayLine & "' y2='16' style='stroke:" & _ColourTL & ";stroke-width:" & _WidtTL & "' />
</svg>"
VAR _SVG =
    SWITCH (
        TRUE (),
        MAX ( TasksHeader[TaskIsProjectSummary] ) = 1, _PST,
        MAX ( TasksHeader[TaskIsSummary] ) = 1, _ST,
        MAX ( TasksHeader[TaskIsMilestone] ) = 1, _MS,
        _T
    )
VAR _CheckBrowseDepth = [TaskBrowseDepth] <= [TaskRowDepth]
VAR _Result =
    IF ( _CheckBrowseDepth && NOT ( ISBLANK ( _DurationP ) ), _SVG )
RETURN
    _Result

The time bar is created via the following code:

GanttTimeLine = 
VAR _MinDate =
    CALCULATE ( MIN ( Tasks[TaskStartDate] ), ALLSELECTED ( TasksHeader ) )
VAR _MaxDate =
    CALCULATE ( MAX ( Tasks[TaskFinishDate] ), ALLSELECTED ( TasksHeader ) )
VAR _MaxDuration =
    DATEDIFF ( _MinDate, _MaxDate, DAY )
VAR _Scaling =
    DIVIDE ( 512, _MaxDuration )
VAR _TodayLine =
    DATEDIFF ( _MinDate, TODAY (), DAY ) * _Scaling
VAR _FontSM = "6.5"
VAR _ColourTimeL = "rgb(235,235,235)"
VAR _ColourTL = "rgb(255,190,0)"
VAR _WidtTL = "0.5"
VAR _YLines =
    CONCATENATEX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                FILTER (
                    ADDCOLUMNS (
                        GENERATESERIES ( _MinDate, _MaxDate, 1 ),
                        "MinDate", _MinDate,
                        "EndofMonth", IF ( [Value] = DATE ( YEAR ( [Value] ), 12, 31 ), [Value] )
                    ),
                    NOT ( ISBLANK ( [EndofMonth] ) )
                ),
                "PosMLine", DATEDIFF ( [MinDate], [EndofMonth], DAY ) * _Scaling
            ),
            "SVGLine",
                VAR _PosMLine = [PosMLine]
                RETURN
                    "<line x1='" & _PosMLine & "' y1='0' x2='" & _PosMLine & "' y2='33' style='stroke:" & _ColourTimeL & "; stroke-width:" & _WidtTL & "' />"
        ),
        [SVGLine]
    )
VAR _YText =
    CONCATENATEX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                FILTER (
                    ADDCOLUMNS (
                        GENERATESERIES ( _MinDate, _MaxDate, 1 ),
                        "MinDate", _MinDate,
                        "EndofMonth", IF ( [Value] = DATE ( YEAR ( [Value] ), 12, 31 ), [Value] )
                    ),
                    NOT ( ISBLANK ( [EndofMonth] ) )
                ),
                "PosMLine", DATEDIFF ( [MinDate], [EndofMonth], DAY ) * _Scaling,
                "YearText", YEAR ( [EndofMonth] ) + 1
            ),
            "SVGText",
                VAR _PosMLine = [PosMLine]
                VAR _Year = [YearText]
                RETURN
                    "<text x='" & _PosMLine + 5 & "' y='13' font-family='Arial' font-size='10' fill='" & _ColourTimeL & "'>" & _Year & "</text>"
        ),
        [SVGText]
    )
VAR _MLines =
    CONCATENATEX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                FILTER (
                    ADDCOLUMNS (
                        GENERATESERIES ( _MinDate, _MaxDate, 1 ),
                        "MinDate", _MinDate,
                        "EndofMonth", IF ( [Value] = EOMONTH ( [Value], 0 ), [Value] )
                    ),
                    NOT ( ISBLANK ( [EndofMonth] ) )
                ),
                "PosMLine", DATEDIFF ( [MinDate], [EndofMonth], DAY ) * _Scaling
            ),
            "SVGLine",
                VAR _PosMLine = [PosMLine]
                RETURN
                    "<line x1='" & _PosMLine & "' y1='18' x2='" & _PosMLine & "' y2='28' style='stroke:" & _ColourTimeL & "; stroke-width:" & _WidtTL & "' />"
        ),
        [SVGLine]
    )
VAR _MText =
    CONCATENATEX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                ADDCOLUMNS (
                    FILTER (
                        ADDCOLUMNS (
                            GENERATESERIES ( _MinDate, _MaxDate, 1 ),
                            "MinDate", _MinDate,
                            "EndofMonth", IF ( [Value] = EOMONTH ( [Value], 0 ), [Value] )
                        ),
                        NOT ( ISBLANK ( [EndofMonth] ) )
                    ),
                    "PosMLine", DATEDIFF ( [MinDate], [EndofMonth], DAY ) * _Scaling,
                    "PosMLineNext", DATEDIFF ( [MinDate], EOMONTH ( [EndofMonth], + 1 ), DAY ) * _Scaling,
                    "MonthsText", LEFT ( FORMAT ( EOMONTH ( [EndofMonth], + 1 ), "mmm" ), 1 )
                ),
                "Gap", [PosMLineNext] - [PosMLine]
            ),
            "SVGText",
                VAR _PosMText =
                    [PosMLine] + DIVIDE ( [Gap], 2 )
                VAR _MText = [MonthsText]
                RETURN
                    "<text x='" & _PosMText & "' y='26' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _MText & "</text>"
        ),
        [SVGText]
    )
VAR _TL1 = "data&colon;image/svg+xml;utf8,<svg 
    width='512' height='40'
    xmlns='http://www.w3.org/2000/svg'  
    xmlns:xlink='http://www.w3.org/1999/xlink'>
    
        " & _YLines & "
        " & _YText & "
        " & _MLines & "
        " & _MText & "

        <line x1='0' y1='18' x2='512' y2='18' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <line x1='" & _TodayLine & "' y1='0' x2='" & _TodayLine & "' y2='39' style='stroke:" & _ColourTL & ";stroke-width:" & _WidtTL & "' />
        <rect x='0' y='16' width='2' height='5'  fill='rgb(144,142,142)' />
        
    </svg>"
RETURN
    _TL1

My data model is the following:

With the same concept, i have also created a baseline - actual comparison:

In the end one can build a very nice solution with no need of custom visuals.

I hope this provides some useful informations for someone who wants to rebuild it.

Best regards

[PeterMech]

Hi, @ITManuel,

We have been working on implementing your code you have provided. However, we have changed to Primavera P6 and will have to modify the code to suit. I have relinquished the MS Project Web environment and am unable to verify some of the data columns. In your code you refer to:

VAR _CheckBrowseDepth = [TaskBrowseDepth] <= [TaskRowDepth]

Where is he TaskBrowseDepth and TaskRowDepth comming from?

Thanks,

[ITManuel]

Hi @PeterMech ,

the [TaskBrowseDepth] and [TaskRowDepth] are measures with regards to the parent-child hierarchy pattern. They are required to check on which level in the hierarchy you currently browse in the matrix. 

The pattern is well described here https://www.daxpatterns.com/parent-child-hierarchies/

Kind regards

[pnm_100]

Hey, love this! Is there any way to get a copy of the .pbix file? I am trying to make one similar but can't seem to get it to work. It is also hard to see the table structure in the picture. Thanks

[ITManuel]

HI @pnm_100 ,

I'm afraid I cannot share my pbix file but if you want to share yours I can have a look at it.

Br

[pnm_100]

Hey, I might be able to share something when I get back to the office on Monday. In the meantime, could you maybe share the data you used? I think my data structure is incorrect and I'm unsure what some of the columns in your code represents. The bar columns in my chart just shows the code, but the plus/minus buttons and webs structure seem to show properly

[ITManuel]

As I exlained in my first post, I'm using data from MS Project schedules which are stored in the so called project web app, a sort of cloud for MS project schedules. 

The columns I'm using in the code are actually what their name suggests, I think the column names are quite clear.

If the column in which you use the SVG measure shows only code, it means that the data category of your measure has not been set correctly. Please select the measure and set "Image URL" for data category.

Br

[pnm_100]

Hey, thanks for the assistance. I feel like I am so close to getting this right. I have read and re-read the instructions many times, but I still can't get a hang of what is required for the date columns. I created a column in the table, created the GanttTimelineColumn measure and set the column equal to the measure. This is the outcome in the screenshot. If you have any ideas, it would be great! If not, thanks again for your help up to this point, I appreciate it!

[ITManuel]

Hi,

you should not be using anything in the columns of the matrix.

This is the data I'm using in the matrix visual. 

On the rows I have the task names parent child hierarchy structure with different levels:

Project | Tasks =Level 1 = 

Level1 = RELATED ( ProjectsPWA[Project] )

Level 2 = 

Level2 = 
VAR _LevelKey = PATHITEM ( TasksHeader[TaskPath], 2, TEXT )
VAR _Result = LOOKUPVALUE ( TasksHeader[Task], TasksHeader[TaskID], _LevelKey )
RETURN
    _Result

Level 3 = 

Level3 = 
VAR _LevelKey = PATHITEM ( TasksHeader[TaskPath], 3, TEXT )
VAR _Result = LOOKUPVALUE ( TasksHeader[Task], TasksHeader[TaskID], _LevelKey )
RETURN
    _Result

and so on.......

The "." ( last field under values ) is my GanttSVG measure.

The Timeline above is a separate table which displays the GanttTimeLine measure. 

"." = GanttTimeLine measure

So we have 1 matrix and 1 table which sits in the column header of the Gantt SVG column above the matrix. 

Hope this helps.

Br

[Cerber666]

Hi, @ITManuel 

I used your solution, but my visual doesn't work. What do you think might be the problem?

[ITManuel]

Hi @Cerber666 ,

you should not use dates in the columns of the matrix visual, please try to remove it.

The Gantt chart is nothing else than a large column in the matrix visual. The column should not be sliced by anything.

Br

[Patricio479]

Hello, dear @ITManuel 

First of all, I just have one word for this: wow!

Secondly, I would like to thank you and show my biggest appreciation as this might have taken lots of time to put together: since the start of the project to this final very-detailed post.
I am still giving my first steps into understanding how the SVG and coding/dax works, so I might reach out to you in the future in case I have questions.

Thanks again for your effort and time to teach us how to do a very professional Gantt chart using the tools within PowerBI... I am pretty sure that this will help not only me but a considerable amount of people willing to learn a more advanced PowerBI.

Have a nice rest of the day, dear @ITManuel .

Kind regards,
Patricio.



 

[ITManuel]

Hi @Patricio479 ,

thank you very much, you are welcome. I have got to solutions for the issues I had with Power BI so many times via this community, so its time to give something back. 😊

Feel free to reach out for any further issues.

Best regards

[bmscriven]

Edit: Figured this out in the end. 

>> Create measure for timeline SVG Visual > Add new column to table containing dates data >> New Column = [Gant Timeline Measure] >> Drag new column into the 'columns' panel of the matrix visual. 
🙂 

Firstly, Kudos to this amazing post. Theres so much in this post its amazing. 

Question to you ITManuel about the calendar SVG placement on the visual: Can you go into a bit more detail in how did you got the Calendar SVG to appear as the column header itself?  I've re-read your detailed post a thousand times, but for the life of me I cant figure it out. 

Thanks in advance 🙂

[ITManuel]

Hi @bmscriven,

• The Gantt chart is a large column in the standard PBi matrix visual
• I have named the column header of the Gantt chart column "." (dot) so that no large name appears in the column header, just the "."
• I have covered the "." with a shape matching the background colour so the "." disappears
• Then I have placed a standard table visual in the column header of the Gantt chart column. With the code I have provided above, an SVG showing Years and months is visualized in this table which is positioned in the column header, so it appears as it would be an integral part of the visulization. In fact is is just a table visual above the underlaying matrix visual.

Hope this helps

Br