Forum Discussion

DennisGaida's avatar
DennisGaida
Frequent Visitor
8 years ago
Solved

Dynamic filtering/slicing if text contains

I have the following data:   Date Text 2018-11-01 This is some random text 2018-04-03 Some test mumbling here 2018-02-01 Whatever we write here I have a visual (line chart) displayin...
Need Help
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Hi DennisGaida

 

May be you could use a MEASURE like

 

Measure =
VAR searchvalue =
    SEARCH (
        SELECTEDVALUE ( Text_Queries[Column1] ),
        SELECTEDVALUE ( Table1[Text] ),
        ,
        BLANK ()
    )
RETURN
    IF ( searchvalue > 0, "Found" )

See the attached file. I am not sure if this what you need

 

  • DennisGaida's avatar
    DennisGaida
    Frequent Visitor
    8 years ago

    This is perfect!

     

    Meanwhile I found this solution: http://blog.pragmaticworks.com/power-bi-problem-design-solution-text-search-slicer which uses an "IsFiltered column:

    IsFiltered = 
IF(
    SUMX('Slicer',
        FIND(
            'Slicer'[Departments],
            MAX('Department Goals'[Affected Departments]),,0)) > 0,
            "True",
            "False"
)

    This also works, but is uses an additional Visual Level Filter (IsFiltered) - this in turn doesn't find "all" records if I don't select anything (because IsSelected needs to be true). I added an empty Query_Text to find all elements.

     

    Your solution is a lot cleaner and I love SELECTEDVALUE(), didn't know this existed.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Most powerful code is here.

      • renaudstaessens's avatar
        renaudstaessens
        Icon for Helper I rankHelper I
        5 years ago

        Anonymous  : where is the code David 🙂 there's no hyperlink with the word here

  • DennisGaida's avatar
    DennisGaida
    Frequent Visitor
    8 years ago

    One the same example: Do you know how to COUNT the number of rows currently filtered?

     

    Using a Card with a Count on e.g. Table1[Date] it always returns 3 no matter how I filter. And cards cannot have visual filters applied unfortunately.

      • Scott's avatar
        Scott
        Frequent Visitor
        8 years ago

        Hi Zubair_Muhammad,

         

        The Measure for the list works great, but whenever I select more than one item from Column1, the filter appears to cancel out and show all results as if no filter was applied. Selecting 1 filter row from Column1 works as expected. How can we enable multi-select? 

  • ankitkalsara's avatar
    ankitkalsara
    Icon for Helper I rankHelper I
    7 years ago

    Hi Zubair_Muhammad 

     

    The solution works perfeclty well. However I need to search on the exact keywork which is not happening.

     

    For e.g. I want to search for keyword "Star" the result is giving all the text having words "starting", "starts" etc.

     

    Any suggestion on the above issue.

     

    Thanks.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks for the soluton. however, It works for single selection, but it does not work with multiple selection, do you what is the possible reason for this?