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))