Forum Discussion

Walter_Mangy's avatar
Walter_Mangy
New Member
1 year ago
Solved

Help with DAX Measure to Insert Line Breaks Without Splitting Words (Max 14 Chars per Line)

Hi everyone, I'm working on a DAX measure in Power BI that aims to format a string column ([name]) by inserting a line break (\n or UNICHAR(10)) every 14 characters without breaking words. Here’s t...
  • techies's avatar
    techies
    1 year ago

    Hi Walter_Mangy please try this calculated column

     

    Formatted Name =
    VAR MaxLen = 14
    VAR Words = SUBSTITUTE(TRIM(NamesTable[name]), " ", "|")
    VAR WordCount = PATHLENGTH(Words)
    VAR WordTable =
        ADDCOLUMNS(
            GENERATESERIES(1, WordCount),
            "Word", PATHITEM(Words, [Value]),
            "Length", LEN(PATHITEM(Words, [Value]))
        )

    VAR LineBuilder =
        ADDCOLUMNS(
            WordTable,
            "LineNum",
            VAR ThisIndex = [Value]
            VAR RunningLen =
                SUMX(
                    FILTER(WordTable, [Value] < ThisIndex),
                    [Length] + 1
                ) + [Length]
            RETURN
                QUOTIENT(RunningLen - 1, MaxLen) + 1
        )

    VAR ResultTable =
        ADDCOLUMNS(
            SUMMARIZE(LineBuilder, [LineNum]),
            "LineText",
            CONCATENATEX(
                FILTER(LineBuilder, [LineNum] = EARLIER([LineNum])),
                [Word],
                " "
            )
        )

    RETURN
        CONCATENATEX(ResultTable, [LineText], UNICHAR(10))
     

     

techies's avatar
techies
Super User
1 year ago

Hi Walter_Mangy please try this calculated column

 

Formatted Name =
VAR MaxLen = 14
VAR Words = SUBSTITUTE(TRIM(NamesTable[name]), " ", "|")
VAR WordCount = PATHLENGTH(Words)
VAR WordTable =
    ADDCOLUMNS(
        GENERATESERIES(1, WordCount),
        "Word", PATHITEM(Words, [Value]),
        "Length", LEN(PATHITEM(Words, [Value]))
    )

VAR LineBuilder =
    ADDCOLUMNS(
        WordTable,
        "LineNum",
        VAR ThisIndex = [Value]
        VAR RunningLen =
            SUMX(
                FILTER(WordTable, [Value] < ThisIndex),
                [Length] + 1
            ) + [Length]
        RETURN
            QUOTIENT(RunningLen - 1, MaxLen) + 1
    )

VAR ResultTable =
    ADDCOLUMNS(
        SUMMARIZE(LineBuilder, [LineNum]),
        "LineText",
        CONCATENATEX(
            FILTER(LineBuilder, [LineNum] = EARLIER([LineNum])),
            [Word],
            " "
        )
    )

RETURN
    CONCATENATEX(ResultTable, [LineText], UNICHAR(10))
 

 

Walter_Mangy's avatar
Walter_Mangy
New Member
1 year ago

Thanks a lot techies techies, much appreciated!