"The specified query is too complex to be evaluated as a single statement." Error with SVG code

[ITManuel]

Hi,

I'm working a Gantt chart using the standard Power BI Matrix visual with SVG code to visualize the bars via SVG's. The bars work just fine, I'm now working on a time bar which should provide information about the year and the months.

The time bar is generated via the following DAX | SVG code and is visualized in the standard Power BI table chart, posizioned right in the column header of the matrix visual. I want to cover the years and months for max. 10 years so 132 months, consequently the code for the SVG was getting quite long ( close to 1500 rows ). Adding any further line to the code is resulting in the error : "The specified query is too complex to be evaluated as a single statement." Is there any way to solve this, the code is very repetitive since any line and object in the SVG time bar has to be created separately. 

TestTimeLine = 
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 _EndofFY =
    -- End of first Year
    DATE ( YEAR ( _MinDate ), 12, 31 )
VAR _EndofNY1 =
    -- End of next year 1
    DATE ( YEAR ( _MinDate ) + 1, 12, 31 )
VAR _EndofNY2 =
    DATE ( YEAR ( _MinDate ) + 2, 12, 31 )
VAR _EndofNY3 =
    DATE ( YEAR ( _MinDate ) + 3, 12, 31 )
VAR _EndofNY4 =
    DATE ( YEAR ( _MinDate ) + 4, 12, 31 )
VAR _EndofNY5 =
    DATE ( YEAR ( _MinDate ) + 5, 12, 31 )
VAR _EndofNY6 =
    DATE ( YEAR ( _MinDate ) + 6, 12, 31 )
VAR _EndofNY7 =
    DATE ( YEAR ( _MinDate ) + 7, 12, 31 )
VAR _EndofNY8 =
    DATE ( YEAR ( _MinDate ) + 8, 12, 31 )
VAR _EndofNY9 =
    DATE ( YEAR ( _MinDate ) + 9, 12, 31 )
VAR _EndofNY10 =
    DATE ( YEAR ( _MinDate ) + 10, 12, 31 )
VAR _FYLine =
    DATEDIFF ( _MinDate, _EndofFY, DAY ) * _Scaling
VAR _NY1Line =
    DATEDIFF ( _MinDate, _EndofNY1, DAY ) * _Scaling
VAR _NY2Line =
    DATEDIFF ( _MinDate, _EndofNY2, DAY ) * _Scaling
VAR _NY3Line =
    DATEDIFF ( _MinDate, _EndofNY3, DAY ) * _Scaling
VAR _NY4Line =
    DATEDIFF ( _MinDate, _EndofNY4, DAY ) * _Scaling
VAR _NY5Line =
    DATEDIFF ( _MinDate, _EndofNY5, DAY ) * _Scaling
VAR _NY6Line =
    DATEDIFF ( _MinDate, _EndofNY6, DAY ) * _Scaling
VAR _NY7Line =
    DATEDIFF ( _MinDate, _EndofNY7, DAY ) * _Scaling
VAR _NY8Line =
    DATEDIFF ( _MinDate, _EndofNY8, DAY ) * _Scaling
VAR _NY9Line =
    DATEDIFF ( _MinDate, _EndofNY9, DAY ) * _Scaling
VAR _NY10Line =
    DATEDIFF ( _MinDate, _EndofNY10, DAY ) * _Scaling
VAR _NY1 =
    YEAR ( _MinDate ) + 1
VAR _NY2 =
    YEAR ( _MinDate ) + 2
VAR _NY3 =
    YEAR ( _MinDate ) + 3
VAR _NY4 =
    YEAR ( _MinDate ) + 4
VAR _NY5 =
    YEAR ( _MinDate ) + 5
VAR _NY6 =
    YEAR ( _MinDate ) + 6
VAR _NY7 =
    YEAR ( _MinDate ) + 7
VAR _NY8 =
    YEAR ( _MinDate ) + 8
VAR _NY9 =
    YEAR ( _MinDate ) + 9
VAR _NY10 =
    YEAR ( _MinDate ) + 10
VAR _EndofFM =
    EOMONTH ( _MinDate, 0 ) -- End of first month
VAR _EndofNM1 =
    EOMONTH ( _MinDate, 1 ) -- End of next months 1
VAR _EndofNM2 =
    EOMONTH ( _MinDate, 2 )
VAR _EndofNM3 =
    EOMONTH ( _MinDate, 3 )
VAR _EndofNM4 =
    EOMONTH ( _MinDate, 4 )
VAR _EndofNM5 =
    EOMONTH ( _MinDate, 5 )
VAR _EndofNM6 =
    EOMONTH ( _MinDate, 6 )
VAR _EndofNM7 =
    EOMONTH ( _MinDate, 7 )
VAR _EndofNM8 =
    EOMONTH ( _MinDate, 8 )
VAR _EndofNM9 =
    EOMONTH ( _MinDate, 9 )
VAR _EndofNM10 =
    EOMONTH ( _MinDate, 10 )


..... to VAR _EndofNM132


VAR _FMLine =
    DATEDIFF ( _MinDate, _EndofFM, DAY ) * _Scaling
VAR _NM1Line =
    DATEDIFF ( _MinDate, _EndofNM1, DAY ) * _Scaling
VAR _NM2Line =
    DATEDIFF ( _MinDate, _EndofNM2, DAY ) * _Scaling
VAR _NM3Line =
    DATEDIFF ( _MinDate, _EndofNM3, DAY ) * _Scaling
VAR _NM4Line =
    DATEDIFF ( _MinDate, _EndofNM4, DAY ) * _Scaling
VAR _NM5Line =
    DATEDIFF ( _MinDate, _EndofNM5, DAY ) * _Scaling
VAR _NM6Line =
    DATEDIFF ( _MinDate, _EndofNM6, DAY ) * _Scaling
VAR _NM7Line =
    DATEDIFF ( _MinDate, _EndofNM7, DAY ) * _Scaling
VAR _NM8Line =
    DATEDIFF ( _MinDate, _EndofNM8, DAY ) * _Scaling
VAR _NM9Line =
    DATEDIFF ( _MinDate, _EndofNM9, DAY ) * _Scaling
VAR _NM10Line =
    DATEDIFF ( _MinDate, _EndofNM10, DAY ) * _Scaling

..... to VAR _NM132Line


VAR _NM1TextP =
    -- Months Text position
    _FMLine
        + DIVIDE ( _NM1Line - _FMLine, 2 )
VAR _NM2TextP =
    _NM1Line
        + DIVIDE ( _NM2Line - _NM1Line, 2 )
VAR _NM3TextP =
    _NM2Line
        + DIVIDE ( _NM3Line - _NM2Line, 2 )
VAR _NM4TextP =
    _NM3Line
        + DIVIDE ( _NM4Line - _NM3Line, 2 )
VAR _NM5TextP =
    _NM4Line
        + DIVIDE ( _NM5Line - _NM4Line, 2 )
VAR _NM6TextP =
    _NM5Line
        + DIVIDE ( _NM6Line - _NM5Line, 2 )
VAR _NM7TextP =
    _NM6Line
        + DIVIDE ( _NM7Line - _NM6Line, 2 )
VAR _NM8TextP =
    _NM7Line
        + DIVIDE ( _NM8Line - _NM7Line, 2 )
VAR _NM9TextP =
    _NM8Line
        + DIVIDE ( _NM9Line - _NM8Line, 2 )
VAR _NM10TextP =
    _NM9Line
        + DIVIDE ( _NM10Line - _NM9Line, 2 )

..... to VAR _NM132TextP

VAR _TodayLine =
    DATEDIFF ( _MinDate, TODAY (), DAY ) * _Scaling
VAR _FM =
    LEFT ( FORMAT ( _EndofFM, "mmm" ), 1 )
VAR _NM1 =
    LEFT ( FORMAT ( _EndofNM1, "mmm" ), 1 )
VAR _NM2 =
    LEFT ( FORMAT ( _EndofNM2, "mmm" ), 1 )
VAR _NM3 =
    LEFT ( FORMAT ( _EndofNM3, "mmm" ), 1 )
VAR _NM4 =
    LEFT ( FORMAT ( _EndofNM4, "mmm" ), 1 )
VAR _NM5 =
    LEFT ( FORMAT ( _EndofNM5, "mmm" ), 1 )
VAR _NM6 =
    LEFT ( FORMAT ( _EndofNM6, "mmm" ), 1 )
VAR _NM7 =
    LEFT ( FORMAT ( _EndofNM7, "mmm" ), 1 )
VAR _NM8 =
    LEFT ( FORMAT ( _EndofNM8, "mmm" ), 1 )
VAR _NM9 =
    LEFT ( FORMAT ( _EndofNM9, "mmm" ), 1 )
VAR _NM10 =
    LEFT ( FORMAT ( _EndofNM10, "mmm" ), 1 )

..... to VAR _NM132

VAR _FontSM = "6.5"
VAR _ColourTimeL = "rgb(235,235,235)"
VAR _ColourTL = "rgb(255,190,0)"
VAR _WidtTL = "0.5"
VAR _TL1 = "data&colon;image/svg+xml;utf8,<svg 
    width='512' height='35'
    xmlns='http://www.w3.org/2000/svg'  
    xmlns:xlink='http://www.w3.org/1999/xlink'>
 
        <line x1='0' y1='15' x2='512' y2='15' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />

        <line x1='" & _FYLine & "' y1='0' x2='" & _FYLine & "' y2='35' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' /> 
        <text x='" & _FYLine + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _NY1 & "</text>

        <line x1='" & _NY1Line & "' y1='0' x2='" & _NY1Line & "' y2='35' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NY1Line + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _NY2 & "</text>

        <line x1='" & _NY2Line & "' y1='0' x2='" & _NY2Line & "' y2='15' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NY2Line + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _NY3 & "</text>

        <line x1='" & _NY3Line & "' y1='0' x2='" & _NY3Line & "' y2='15' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NY3Line + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _NY4 & "</text>

        <line x1='" & _NY4Line & "' y1='0' x2='" & _NY4Line & "' y2='15' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NY4Line + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _NY5 & "</text>

        <line x1='" & _NY5Line & "' y1='0' x2='" & _NY5Line & "' y2='15' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NY5Line + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _NY6 & "</text>

        <line x1='" & _NY6Line & "' y1='0' x2='" & _NY6Line & "' y2='15' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NY6Line + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _NY7 & "</text>

        <line x1='" & _NY7Line & "' y1='0' x2='" & _NY7Line & "' y2='15' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NY7Line + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _NY8 & "</text>

        <line x1='" & _NY8Line & "' y1='0' x2='" & _NY8Line & "' y2='15' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NY8Line + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _NY9 & "</text>

        <line x1='" & _NY9Line & "' y1='0' x2='" & _NY9Line & "' y2='15' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NY9Line + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _NY10 & "</text>

        <line x1='" & _FMLine & "' y1='15' x2='" & _FMLine & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM1TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM1 & "</text>

        <line x1='" & _NM1Line & "' y1='15' x2='" & _NM1Line & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM2TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM2 & "</text>

        <line x1='" & _NM2Line & "' y1='15' x2='" & _NM2Line & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM3TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM3 & "</text>

        <line x1='" & _NM3Line & "' y1='15' x2='" & _NM3Line & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM4TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM4 & "</text>

        <line x1='" & _NM4Line & "' y1='15' x2='" & _NM4Line & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM5TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM5 & "</text>

        <line x1='" & _NM5Line & "' y1='15' x2='" & _NM5Line & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM6TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM6 & "</text>

        <line x1='" & _NM6Line & "' y1='15' x2='" & _NM6Line & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM7TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM7 & "</text>

        <line x1='" & _NM7Line & "' y1='15' x2='" & _NM7Line & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM8TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM8 & "</text>

        <line x1='" & _NM8Line & "' y1='15' x2='" & _NM8Line & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM9TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM9 & "</text>

        <line x1='" & _NM9Line & "' y1='15' x2='" & _NM9Line & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM10TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM10 & "</text>

        <line x1='" & _NM10Line & "' y1='15' x2='" & _NM10Line & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM11TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM11 & "</text>

   	...... To be continued to 132

        <line x1='" & _TodayLine & "' y1='0' x2='" & _TodayLine & "' y2='35' style='stroke:" & _ColourTL & ";stroke-width:" & _WidtTL & "' />

    </svg>"
RETURN
    _TL1

Br

[ITManuel · Accepted solution]

The issue is solved with a new, much more efficient code. 😀

Thanks anyway

TimeLine = 
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='15' 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='10' font-family='Arial' font-size='9' 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='15' x2='" & _PosMLine & "' y2='25' 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='22' 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='35'
    xmlns='http://www.w3.org/2000/svg'  
    xmlns:xlink='http://www.w3.org/1999/xlink'>
        " & _YLines & "
        " & _YText & "
        " & _MLines & "
        " & _MText & "
        <line x1='" & _TodayLine & "' y1='0' x2='" & _TodayLine & "' y2='35' style='stroke:" & _ColourTL & ";stroke-width:" & _WidtTL & "' />

    </svg>"
RETURN
    _TL1