cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

DAX Replace repetitive string with one string

Hi Everyone,


I have a column with the click flow of a user on a specific day: 

ClickFlowColumn = CALCULATE(CONCATENATEX(ActivitiesTable, ActivitiesTable[ActivityType], ", "), ALLEXCEPT(ActivitiesTable, ActivitiesTable[UserId], ActivitiesTable[Date]))


BUT, When activities repeat themselves, it becomes impossible to visualise or group the data. I would like to replace repetitive strings (activities) with one single string:

 

What I haveWhat I want
ClickFlowColumnClickFlowColumn
click , click, preview, insert, insert, insertclick, preview, insert
click, search, search, search, previewclick, search, preview

 

Dax only please, because this is a calculated column.

 

Some help would be much appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@Analyst1991 

What do you change your formula like this?

ClickFlowColumn =
CALCULATE (
    CONCATENATEX (
        VALUES ( ActivitiesTable[Activity Type] ),
        ActivitiesTable[Activity Type],
        ", "
    ),
    ALLEXCEPT ( ActivitiesTable, ActivitiesTable[UserId], ActivitiesTable[Date] )
)

Fowmy_0-1598529538176.png

 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

@Analyst1991 , three functions in power query should to it

 

Text.Split
https://docs.microsoft.com/en-us/powerquery-m/text-split

List.Distinct
https://docs.microsoft.com/en-us/powerquery-m/list-distinct

Text.Combine
https://docs.microsoft.com/en-us/powerquery-m/text-combine



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User I
Super User I

have you tried use the contcatenex function?
CONCATENATEX(VALUES(Table1[Column1]),[Column1])




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

Hi @vanessafvg 

 

That worked!

Highlighted

@amitchandak thank you, but I'm working with calculated columns, so unfortunately Power Query won't solve my problem.

Highlighted

@Analyst1991 

What do you change your formula like this?

ClickFlowColumn =
CALCULATE (
    CONCATENATEX (
        VALUES ( ActivitiesTable[Activity Type] ),
        ActivitiesTable[Activity Type],
        ", "
    ),
    ALLEXCEPT ( ActivitiesTable, ActivitiesTable[UserId], ActivitiesTable[Date] )
)

Fowmy_0-1598529538176.png

 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

View solution in original post

Highlighted

@Fowmy  That worked! Thank you! 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors