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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!