Replacing a substring with selected value

[AviramWeiss]

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?

[tamerj1]

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

[AviramWeiss]

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).

[tamerj1]

@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?

[AviramWeiss]

@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:

I defined a new column in table phrases:

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

It works fine:

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):

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

[tamerj1 · 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] )
)

[AviramWeiss]

Thank you very much, @tamerj1 .

Now it works like a charm.