Use values in Input Slicer (former Text slicer) in DAX

[IMett]

Hi all,

is there a way to create a measure which is based on the text value inside of the new Input Slicer in PBI Desktop?
I would like to display a part of the text which is around or just before the position where the search value is located.

AI suggests to query an internal table where the search value is automatically stored with selectedvalue(), but I can't figure out whether such a table indeed exists somewhere and how it is supposed to be named.

Spoiler

Power BI Text Slicer Visual use input value in dax

In Power BI, when you use a Text Slicer (a slicer visual configured for text values), you often want to use the selected slicer value(s) inside a DAX measure or calculated column for dynamic calculations or filtering.

Here’s how you can capture the selected value(s) from a Text Slicer and use it in DAX:

---

1. Using SELECTEDVALUE for Single Selection

If your slicer allows only a single selection (or you want to consider only one selected value), you can use:

SelectedText = SELECTEDVALUE('Table'[Column], "Default Value")

• 'Table'[Column] is the column you use in the slicer.
• "Default Value" is optional, used if no selection or multiple selections are made.

  This returns the selected text or a default if none or multiple are selected.

[IMett]

Btw - what I actually want to achieve is to parse a Power Query Statement in a way that I can extract the adressed datasources on a detail level (a data lineage task).
The keyword search is rather a workaround since I wasn't able to provide a pattern-based logic yet, which would allow me to extract file, folder names, urls, database table names etc. directly from the M Code.

[IMett]

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. 

[ChielFaber · Accepted solution]

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

[GeraldGEmerick]

@IMett You need to add a column to your Field input for your Input Slicer first. Then you can use SELECTEDVALUE on that column. Using the Input Slicer without a column specified doesn't slice anything or really do anything at all.