The Replacements

Greg_Deckler — Wed, 11 Aug 2021 13:56:48 GMT

This one falls solidly into the "there are better ways to do this" camp. Essentially replace multiple values in a string of text.

Comes from this thread. This is basically Text to Table with a different twist.

Measure = VAR __Separator = "*" VAR __SearchText = MAX('Table2'[Column1]) VAR __Len = LEN(__SearchText) VAR __Count = __Len - LEN(SUBSTITUTE(__SearchText,__Separator,"")) + 1 VAR __Table = ADDCOLUMNS( ADDCOLUMNS( GENERATESERIES(1,__Count,1), "__Word", VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|",IF([Value]=1,1,[Value]-1)) VAR __Start = SWITCH(TRUE(), __Count = 1,1, [Value] = 1,1, FIND("|",__Text)+1 ) VAR __End = SWITCH(TRUE(), __Count = 1,__Len, [Value] = 1,FIND("|",__Text) - 1, [Value] = __Count,__Len, FIND(__Separator,__Text,__Start)-1 ) VAR __Word = MID(__Text,__Start,__End - __Start + 1) RETURN __Word ), "__Replaced",LOOKUPVALUE('Table'[Description],'Table'[Code],[__Word]) ) RETURN CONCATENATEX(__Table,[__Replaced],__Separator,[Value])

eyJrIjoiN2ZlZDc0MzctMDg5NC00NzM1LTk1OTMtNDdlOTkxZmNhMjg3IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9&pageName=ReportSection

Re: The Replacements

Greg_Deckler — Mon, 30 Aug 2021 13:53:14 GMT

I revisited this solution after I remembered my Mythical DAX Index. A perhaps more elegant approach to Text to Table if you don't have | characters in your text is this:

    VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|")
    VAR __Table = 
        ADDCOLUMNS(
            GENERATESERIES(1,__Count,1),
            "__Word",PATHITEM(__Text,[Value],TEXT)
        )

If you did have | characters in your text, you would have to SUBSTITUTE them out and then SUBSTITUTE them back in for each word which could get a little messy.

topic Re: The Replacements in Quick Measures Gallery

The Replacements

Re: The Replacements