Forum Discussion
Use values in Input Slicer (former Text slicer) in DAX
- 6 months ago
I don't think this can be done. You can use a second independent table with search words, but that will be pretty cumbersome I believe.
Depending on your use case (service/desktop). I might provide somewhat of a solution although it might not be exactly what you're looking for.
I created some dummy data and two parameters in Powerquery:
Pm_search_word:"fox" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]
Pm_words_before:
2 meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]
The dummy table:
let
Source = #table(
{"ID","FullText"},
{
{1, "The quick brown fox jumps over the lazy dog"},
{2, "Power BI input slicer can filter text values dynamically"},
{3, "DAX measures operate on filter context"},
{4, "Sometimes the search term appears in the middle of the sentence"},
{5, "SelectedValue only works with a single value in context"},
{6, "A disconnected parameter table is often a workaround"},
{7, "This example contains the word Power somewhere"},
{8, "Searching text inside long sentences can be challenging"},
{9, "The search value might appear multiple times in the text"},
{10, "Power BI developers often experiment with DAX snippets"}
}
),
#"Added Custom" = Table.AddColumn(Source, "Custom", each let
SearchWord = Text.Lower(Pm_search_word),
WordsBefore = Pm_words_before,
Words = Text.Split([FullText], " "),
LowerWords = List.Transform(Words, each Text.Lower(_)),
Pos = List.PositionOf(LowerWords, SearchWord),
StartPos = if Pos < WordsBefore then 0 else Pos - WordsBefore,
Result =
if Pos = -1
then null
else Text.Combine(List.Range(Words, StartPos, Pos - StartPos), " ")
in
Result)
in
#"Added Custom"
If you like to try it with real data you can change the source step to point to your own data.
After saving you get this PowerBI table:
There's not a way to drag this to the canvas but from within PowerBI desktop you can change the Powerquery parameter. Follow the next few steps:
Now you can change the search word and the number of words before. You can even go further and add a parameter to change it to before or after.The downside to this method is that the data needs to be refreshed before the change is visible in the report:
Here I changed the searchword to "search" and words before to three:
Although it's not a direct solution to your answer I hope this provides some insights in what can be achieved.Good luck
Hi ChielFaber , thank you very much for the detailed response and the creative idea, although I think that's not really practicable in real world.
I suppose I will rather try to add some effort on data preparation, so that I will cut my string in several chunks by some delimiter characters, with each chunk written into an own row in the dataset, with some ID variable combining them together.
Then I can just display the complete chunk in which the search item was found, which should be more manageable and meaningful than the whole string for the report users.