Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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.
Thank you very much Allan, this is exactly what i was looking for
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
104 | |
104 | |
89 | |
66 |