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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MBethK
Frequent Visitor

MS Text Filter - How to capture the text entered

Issue -

I am unable to store or capture the text that was entered in the MS Text Filter. This does not work - @KeywordSearchView = SELECTEDVALUE('A'[SearchColumn]).

 

MS Text Filter only supports 1 field. We can only use MS visuals at our organization.

 

My report requirement - have a text search box the user can enter that will search multiple columns in my table, and show a table visual of what column matched, and what the text is in that column.

 

As a workaround, I concatenated 2 fields , SearchColumn since the MS Text filter only allows 1 field for the search. 

 

I am using MS Text filter to search a concatenated column named 'SearchColumn' in Table A.

I have a measure:  @KeywordSearchView = SELECTEDVALUE('A'[SearchColumn])

If user types 'Nurse', and I have a card visual for @KeywordSearchView , the value is Blank. 

The MSText filter is successful in showing results from SearchColumn in a tablevisual that match text entered. But I cannot create a table visual with the 'IF_View_Worked' measure to see which specific column matched (ColumnA or ColumnB & their associated text). 

My table A has:

  • ColumnX (text)
  • ColumnY (text) 
  • SearchColumn = 'A'[ColumnX] & " - " & 'A'[ColumnY]
IF_View_Worked =
VAR KeywordSearchView = [@KeywordSearchView]
VAR ColumnAMatch = CALCULATE(
    COUNTROWS(FILTER('A', CONTAINSSTRING('A'[ColumnX], @KeywordSearchView)))
)
VAR ColumnBMatch = CALCULATE(
    COUNTROWS(FILTER('A', CONTAINSSTRING('A'[ColumnB], @KeywordSearchView)))
)

RETURN
    IF (
        ColumnAMatch > 0 && ColumnBMatch > 0,
        "Matched Both Columns" & ":" & @KeywordSearchView,
        IF (
            ColumnAMatch > 0,
            "Column A Match: " & @KeywordSearchView,
            IF (
                ColumnBMatch > 0,
                "ColumnBMatch: " & KeywordSearchView,"No Match"
                        )
        )
    )

My Environment is Direct Query Mode which limits some of the DAX commands.

 

Any help is appreciated. 

 

2 REPLIES 2
Greg_Deckler
Super User
Super User

@MBethK SELECTEDVALUE won't work because it needs a single value to be returned. You could do something like DISTINCT('A'[SearchColumn]) and then use the IN operator for example.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thank you @Greg_Deckler  regarding my use of SELECTEDVALUE.

 

I was incorrectly trying to use SELECTEDVALUE to capture what text was entered in the MS filter so that I determine if the text matched ColumnX and/or ColumnY, which were concatenated in 'A'[SearchColumn]. 

 
I read your help and tried the  DISTINCT for SearchColumn plus and IN operator. I still couldn't check against what was typed/searched.
If the user types in word 'Nurse' in the MS Filter Text , how do I capture the word 'Nurse' to see if this is the ColumnX, and/or Column Y?
 
The result of typing 'Nurse' in the MS Text Filter will show me all 3 values in Column A, if I put column A on a table Visual. But I would only want  Nurse and Nurse aides. I get Greenhouse because the text filter uses concatenated column (SearchColumn).
I'm concatenating 2 fields to get a match because the text filter supports only 1 field, but I want to know if it matched column #1, and/or Column #2. 
 
ColumnAColumnB     SearchColumn (concatenated ColumnA & ColumnB)
   
Greenhouse        Nursery, Horticulture, Landscape     Greenhouse - Nursery, Horticulture, Landscape
NurseProfession requires University Nurse - Profession requires University 
Nurse AidesLicensed Practical NursesNurse Aides - Licensed Practical Nurses
 
Any additional insight is greatly appreciated .

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors