Forum Discussion
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) displaying all Texts over time. I can find text in a column like so: Find_some = FIND("Some";Table[Text];;0). I can also use this new column to mark days when this specific text has been found (e.g. using a Pulse Chart).
How can I do a dynamic find / find multiple? I want the user to be able to use a slicer / filter to select the word I am filtering, e.g.:
Text Queries: - some - whatever - here
The query list is of course static / based on some fixed lookup table. If the user selects the word "some", all rows containing the word "some" are found - of course I want to use that slicer/filter to dynamically slice the line chart I already have.
Right now I can only think of multiple line charts - each with its own text query, but this isn't very visually appealing.
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
11 Replies
- Zubair_Muhammad
Community Champion
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
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.
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.
- Zubair_Muhammad
Hi DennisGaida
Sorry for late reply
You can use this MEASURE. File attached as well
Countrows = COUNTROWS ( FILTER ( Table1, [Measure] = "Found" ) )
ankitkalsaraHelper I
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.
