Forum Discussion

MBethK's avatar
MBethK
Frequent Visitor
2 years ago

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. 

 

Need Help

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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.

    • MBethK's avatar
      MBethK
      Frequent Visitor
      2 years ago

      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 .