User Input text parameter to update formulas

[Anonymous]

Hello, 

I have a situation where a user wants to be able to search text fields for certain key words so we can analyse how frequently this text or theme is entered into the text fields. 

I have created a meausre that will look for a certain word and tell me when it was first mentioned, most recent and then can calulate various metrics. 

However, it wouldn't be pracical to create these measures for each word that they are interested in. 

What i wanted then was to build a generic report that looks at timeslines / counts etc. and then use a parametre field that the user can update which feeds the specific word into all the measures. 

I think this can't be done using whatif parameters in power BI. Does anyone have any other suggestions?

Many thanks and best wishes

[Anonymous]

When you create the ParameterText parmeter, how did you create the table version?  When I create the parameter, it just shows up as a parameter.  For that reason the measures fail because I haver no parameter table.

[Anonymous]

Hello Allan, 

I wonder if you or anyone could help me. 

I needed to find the first recorded mention using All except (to show for each project or client), so i wrote a formula based on this and find but it didn't let me reference the parameter. 

First Recorded Incident = calculate(min('All Casenotes'[CN Record ID]),ALLEXCEPT('All Casenotes','All Casenotes'[Project Record ID]),filter('All Casenotes',find(text,'All Casenotes'[Casenote text],,0)))

Do you know why?

[Anonymous]

Thank you very much Allan, this is exactly what i was looking for

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

You may create a parameter in Power Query as below.

You may create two measures as below in Power BI Desktop.

Frequency = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        CONTAINSSTRINGEXACT([Text],MAX(ParameterText[ParameterText]))
    )
)

First mentioned = 
CALCULATE(
    MIN('Table'[Date]),
    FILTER(
        ALL('Table'),
        CONTAINSSTRINGEXACT([Text],MAX(ParameterText[ParameterText]))
    )
)

You may click 'Transform data'=>'Edit Parameter' and then click 'Refresh' button to update the parameter's value.

ParameterText="word":

ParameterText="value":

Best Regards

Allan

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