Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
DennisGaida
Frequent Visitor

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.

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
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

 

dynmic.png


Regards
Zubair

Please try my custom visuals

View solution in original post

10 REPLIES 10
Zubair_Muhammad
Community Champion
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

 

dynmic.png


Regards
Zubair

Please try my custom visuals

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.

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.

Hi @DennisGaida

 

Sorry for late reply

 

You can use this MEASURE. File attached as well

 

Countrows =
COUNTROWS ( FILTER ( Table1, [Measure] = "Found" ) )

dyn2.png


Regards
Zubair

Please try my custom visuals

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? 

@Scott

 

Good Question

 

In that case we can adjust the MEASURE as follows

 

Measure =
VAR mycount =
    COUNTROWS (
        FILTER (
            VALUES ( Text_Queries[Column1] ),
            SEARCH ( [Column1], SELECTEDVALUE ( Table1[Text] ),, BLANK () )
        )
    )
RETURN
    IF ( mycount > 0, "found" )

Regards
Zubair

Please try my custom visuals

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
Not applicable

Most powerful code is here.

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

Anonymous
Not applicable


@renaudstaessens wrote:

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


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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.