Forum Discussion
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?
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] )
)
6 Replies
- tamerj1Community Champion
Hi AviramWeiss
Make sure you are fitering the Customer Name from the customer table. Also double check interactions.- AviramWeissHelper I
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).
- tamerj1Community Champion
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?