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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
AviramWeiss
Helper I
Helper I

Replacing a substring with selected value

Hello,

 

I'm creating a personalized letter in Power BI desktop (have to do it using this, because of client's demand...).

I have a customer table (lets say it has only 2 columns: custID and custName), and an independant text-parts table that composes the letter.

In these text-parts, there are place-holders for parameters. They look like "[param-name]".

For this example, I have 2 types of place-holders: [year] and [custName].

So a typical text-part could look like: "During the year of [year], the above-mentioned [custName] has purchased items..."

The report has to replace [year] with a fixed value (2022) and [custName] with a value from customers table, after filtering (page filter or slicer) only one customer.

I created another table, name "params", which holds 2022 as a value, and  managed the [year] substitution well with:

substitute([text],"[year]",calculate(max('params'[value]),filter('params',[param]="year"))).

 

However, when trying to substitute with custName, I always get the unfiltered customer list.

Tried these, with no avail:

- substitute([text],"[custName]",max('customers'[custName]))

- substitute([text],"[custName]",calculate(maxx('customers'[custName]),keepfilters(not(isblank('customers'[custName])))))

- substitute([text],"[custName]",selectedvalue('customers'[custName]))

- substitute([text],"[custName]",maxx(filter('customers',[custName]=selectedvalue('customers'[custName])),'customers'[custName]))

 

Any ideas?

1 ACCEPTED SOLUTION

@AviramWeiss 

Thank you for the clear explanation. Calculated columns do not communicate with the filter context. This has to be a measure

calcCustSubstitute =
SUBSTITUTE (
SELECTEDVALUE ( Phrases[phrase] ),
"[custName]",
SELECTEDVALUE ( customers[custName] )
)

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

Hi @AviramWeiss 
Make sure you are fitering the Customer Name from the customer table. Also double check interactions.

Thank you, @tamerj1 .

I'm using the same field, in the same table, to filter.

As for interactions - as far as I understand, there should be no relationship between the 2 tables, as the text-parts are not specific to a customer. I double checked, and there are no relationships (actually, the text-part table is stand-alone, and is not connected to any other table in the project).

@AviramWeiss 

Interactions are between slicers and visuals snd between different visuals. However, I don't think this is the problem. Can you please share some screenshots to clarify the picture?

@tamerj1 ,

I apologize, but can't send a working file (network limitations...). Here are some screenshots. Hope they explain better than before:

 

Here is my entire database:

1.JPG

 

I defined a new column in table phrases:

calcYearSubstitute = SUBSTITUTE([phrase],"[year]","2022")

It works fine:

2.JPG

As you can see, in both phrases 2 and 3, the substring [year] was replaced by 2022.

 

Added a single-choice slicer based on customers[custName], and defined another column in phrases:

calcCustSubstitute = SUBSTITUTE([phrase],"[custName]",maxx('customers',[custName]))

 

No matter how I slice the customers, the substring [custName] in phrases 1 and 3 is always replaced by "Ringo" (alphabetic max of the customer list):

3.JPG

I want the newly defined column calcCustSubstitute to respond to the slicer on the page, so the substring [custName] would be replaced by "Paul" in this example, and any toher customer when slicing appropriately.

 

Thanks,

Aviram

@AviramWeiss 

Thank you for the clear explanation. Calculated columns do not communicate with the filter context. This has to be a measure

calcCustSubstitute =
SUBSTITUTE (
SELECTEDVALUE ( Phrases[phrase] ),
"[custName]",
SELECTEDVALUE ( customers[custName] )
)

Thank you very much, @tamerj1 .

Now it works like a charm.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.