Forum Discussion

AviramWeiss's avatar
AviramWeiss
Helper I
3 years ago
Solved

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?

  • tamerj1's avatar
    tamerj1
    3 years ago

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

6 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion

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

    • AviramWeiss's avatar
      AviramWeiss
      Helper 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).

      • tamerj1's avatar
        tamerj1
        Community Champion

        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?