Help with string consolidation in DAX.

[Anonymous]

Hello,

I need help figuring out how to accomplish the following using DAX:

1) I have a measure that concatenates strings and produces something like this for example:

"Opening, Opening, Opening, Issue Description, Issue Description, Opening, Opening, Issue Resolution, Issue Description, Close, Close, Issue Description, Close"

2) I want to end up with a simplified sequence by consolidating identical consecutive substrings.  I.e, instead of the string above i would end up with this:

"Opening, Issue Description, Opening, Issue Resolution, Issue Description, Close, Issue Description, Close"

I'm operating on a very large dataset so performance is going to be key.  

Honestly not sure where to even begin so any pointers would be appreciated.  

Thank you

[OwenAuger]

Hi @Anonymous 

This post from @Phil_Seamark provides the starting point for solving this.

If your existing measure is a text string delimited by " ," (space comma), then you could write another measure like this:

Simplified Sequence =
VAR SplitByCharacter = ", "
VAR MyText = <EXISTING SEQUENCE MEASURE>
VAR PositionItem =
    VAR PipeDelimited =
        SUBSTITUTE ( MyText, SplitByCharacter, "|" )
    VAR Length =
        PATHLENGTH ( PipeDelimited )
    VAR Positions =
        SELECTCOLUMNS ( GENERATESERIES ( 1, Length ), "Position", [Value] )
    RETURN
        GENERATE (
            Positions,
            VAR CurrentItem =
                PATHITEM ( PipeDelimited, [Position] )
            VAR PreviousItem =
                PATHITEM ( PipeDelimited, [Position] - 1 )
            RETURN
                FILTER ( { CurrentItem }, [Value] <> PreviousItem )
        )
RETURN
    CONCATENATEX ( PositionItem, [Value], SplitByCharacter, [Position] )

It makes use of PATH functions to extract the items from the text.

Also I used FILTER to remove consecutive duplicates.

Regards,

Owen

[kentyler]

you want to use CONCATENATX and DISTINCT

this post has some explanation https://community.powerbi.com/t5/Desktop/Concatenatex-related-distinct-values/td-p/337412

I'm a personal Power Bi Trainer I learn something every time I answer a question

The Golden Rules for Power BI

1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.