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

     

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

Walter_Mangy , Try using

DAX
nameindeneb =
VAR RawName = [name]
VAR CleanName = TRIM(RawName)
VAR Words = SUBSTITUTE(CleanName, " ", "|")
VAR WordCount =
IF(
NOT ISBLANK(Words),
PATHLENGTH(Words),
0
)
VAR WordList =
ADDCOLUMNS(
GENERATESERIES(1, WordCount),
"Word", PATHITEM(Words, [Value]),
"Length", LEN(PATHITEM(Words, [Value]))
)
VAR MaxChars = 14
VAR Result =
GENERATE(
WordList,
VAR Index = [Value]
VAR Word = [Word]
VAR PrevWords = FILTER(WordList, [Value] < Index)
VAR RunningLength =
SUMX(PrevWords, [Length] + 1) + [Length]
VAR Break = IF(RunningLength + [Length] > MaxChars, UNICHAR(10), " ")
RETURN ROW("TextOut", Word & Break)
)
RETURN
IF(
WordCount = 0,
BLANK(),
CONCATENATEX(Result, [TextOut], "")
)

  • Walter_Mangy's avatar
    Walter_Mangy
    New Member
    1 year ago

    Hi bhanu_gautam thanks for picking this one up. Unfortunately your query doesn't seem like working. Did you try it on some specific text column? Thanks