Forum Discussion

IMett's avatar
IMett
Icon for Helper III rankHelper III
6 months ago
Solved

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

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 befor...
  • ChielFaber's avatar
    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