cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dutifuldax
Helper III
Helper III

User Input text parameter to update formulas

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

 

 

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @dutifuldax 

 

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

f1.png

 

You may create a parameter in Power Query as below.

f2.png

 

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

f3.png

ParameterText="value":

f4.png

 

Best Regards

Allan

 

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

View solution in original post

3 REPLIES 3
dutifuldax
Helper III
Helper III

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?
dutifuldax
Helper III
Helper III

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

v-alq-msft
Community Support
Community Support

Hi, @dutifuldax 

 

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

f1.png

 

You may create a parameter in Power Query as below.

f2.png

 

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

f3.png

ParameterText="value":

f4.png

 

Best Regards

Allan

 

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

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!