Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

To remove duplicate char from string on each row using DAX expression

Hi All,

Need your help to remove duplicate chars from string using DAX expression.

I have used the CONCATENATEX function to concatenate rows values based on some criteria. Due to this my string in single cell becomes as per below: 

Capture d'écran_20221109_211908.png

 

 

 

 

 

Output String using CONCATENATEX function:

123333333333333333455555555555555567

Expected result:

1234567

 

I want to remove duplicate char from my string on each row.  Ihave tried insert values function inside CONCATENATEX  like : CONCATENATEX(table,values(filter(..)),filter(..)) .   But no success.

 

Thanks,

Gop01

1 ACCEPTED SOLUTION

Hi @Anonymous ,

Please update the formula of calculated column [sousOF_] as below and check if that is what you want. You can find the details in the attachment.

sousOF_ = 
CONCATENATEX (
    FILTER (
        VALUES ( Ds[ID] ),
        PATHCONTAINS ( [Lien], EARLIER ( [ID] ) )
            && VALUE ( [ID] ) >= VALUE ( EARLIER ( Ds[ID] ) )
    ),
    [ID]
)

yingyinr_0-1668070578616.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-yiruan-msft
Community Support
Community Support

Hi  @Anonymous ,

You can refer the following links to remove the duplicated values when using CONCATENATEX function:

CONCATENATEX is providing duplicated result even when using values() ,distinct() or related table()
Measures=
CONCATENATEX (
    VALUES ( '# test_DRIVER_DASHBOARD'[CATEGORY] ),
    '# test_DRIVER_DASHBOARD'[CATEGORY],
    ","
)

CONCATENATEX providing unique (distinct) values

yingyinr_1-1668046932524.png

If the above ones can't help you get the expected result, please provide some raw data in the table 'Ds' with Text format and the calculation logic of the calculated column [sousOF_]. It seems that you used PATHCONTAINS function there, is there any hierarchy level exist in the field? It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thank you @v-yiruan-msft  for you response, however i haven't been able to pair values function as you suggested with my filter function. I am enclosing a link to the file. Thanks.

 

https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdrive.google.com%2Fdrive%2Ffolders... 

 

Gop01

Hi @Anonymous ,

Please update the formula of calculated column [sousOF_] as below and check if that is what you want. You can find the details in the attachment.

sousOF_ = 
CONCATENATEX (
    FILTER (
        VALUES ( Ds[ID] ),
        PATHCONTAINS ( [Lien], EARLIER ( [ID] ) )
            && VALUE ( [ID] ) >= VALUE ( EARLIER ( Ds[ID] ) )
    ),
    [ID]
)

yingyinr_0-1668070578616.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Morning! So how would you do this if you need to filter things out based on another table? 

Anonymous
Not applicable

It works. Thank you so much.

mariussve1
Solution Supplier
Solution Supplier

Hi,

 

Could you try to use DISTINCT(), like:

CALCULATE(CONCATENATEX(DISTINCT('table'[column]);'table'[column];", "))

 

Br

Marius

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors