Forum Discussion

Frequent Visitor

3 years ago

Solved

Gantt Chart with SVG objects in a measure and matrix visual

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 thi...

2 years ago

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

ITManuel

Responsive Resident

2 years ago

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.

pnm_100

Frequent Visitor

2 years ago

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
Responsive Resident
2 years ago
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
  Frequent Visitor
  2 years ago
  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
    Responsive Resident
    2 years ago
    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

Forum Discussion

Gantt Chart with SVG objects in a measure and matrix visual

Related content

Multiple measures in matrix by year

Measure to Calculate Objective

Responsive Matrix Visual Object

Unused Objects

Matrix table