Replace a value in a new calculated column

[Mukombo]

Hello guys,

I have a couple of columns I have created using this formula:

County = LOOKUPVALUE (

    'organisation units'[org unit],

    'organisation units'[org unit Id],

    PATHITEM ( 'organisation units'[Path],3))

and it is returning the correct value. From the data source, which I cannot change, the text contains another value that I do not want to show in the final text. Kindly help me with how I can solve this.

Thanks

[Ashish_Mathur · Accepted solution]

Hi,

Share some data and show the expected result.

[Ashish_Mathur · Accepted solution]

Hi,

Share some data and show the expected result.

[Mukombo]

Thanks Ashish,

Though it did not solve my issue from the explanation in the document. But i learned new ideas from the same.

[Mukombo]

Dear Ashish, Thank you for reaching out. See the screenshot below. What i do not want is the "County" to appear in the text.

[Anonymous · Accepted solution]

Hi @Mukombo ,

Judging from the screenshots you provided, it's easy to remove County. You only need to modify it as follows

County =
VAR tt =
    LOOKUPVALUE (
        'organisation units'[org unit],
        'organisation units'[org unit Id], PATHITEM ( 'organisation units'[Path], 3 )
    )
RETURN
    LEFT ( tt, LEN ( tt ) - 6 )

Or create a new calculated column like

NewCounty =
LEFT ( [County], LEN ( County ) - 6 )

Best Regards,

Stephen Tao

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

[Mukombo]

Hey Stephen, this was very helpful. thanks a million!

[Ashish_Mathur]

Hi,

Please use the SUBSTITUTE function - SUBSTITUTE function (DAX)