Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

 

 

Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

5 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
    Anonymous
    Not applicable
    3 years ago

    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.

    • donovanf's avatar
      donovanf
      Regular Visitor
      2 years ago

      You can select the properties option after selecting the parameter and Enable Load.