Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to search and count multiple keywords from a column using DAX

Hello All,

I need help with DAX. I tried creating calculated columns and mesures with search() but it is not giving me correct result.

Created below dummy table to somehow simplify my question.

For Example below is my table. I have to search and count for vowels "a","e","i","o","u".

If I get max 3 or more vowels in my search string, i will score it with 3 pts(maximum).

If 2 vowels, score it 2 pts.

if 1 vowels, score it 1 pts.

else 0 pts.

Note: Have to consider only unique vowels in a string. Duplicates should be considered as 1.

 

IssueNoSearchText
1dream
2each
3cat
4jeep
5adiue
6aeon

 

Output :

 

Issue NoSearch Text    Count Vowels Score
1dream2
2each2
3cat1
4jeep1
5adiue3
6aeon3

 

Thanks In Advance.

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Anonymous - Try this:

     

    Column = 
    VAR __Vowels = { "a", "e", "i", "o", "u" }
    VAR __Table = SELECTCOLUMNS(ADDCOLUMNS(GENERATESERIES(1,LEN([SearchText]),1),"Letter",MID([SearchText],[Value],1)),"Letter",[Letter])
    VAR __Intersect = INTERSECT(__Table,__Vowels)
RETURN
    COUNTROWS(DISTINCT(__Intersect))

    PBIX is attached below signature. Table 6.

  • Anonymous's avatar
    Anonymous
    6 years ago

    HI Anonymous ,

     

     

    Create a Calculated Column

     

    Count = 
VAR a =SUBSTITUTE('Table'[SearchText], "a", "",1)
VAR e = SUBSTITUTE( a, "e", "",1)
VAR u = SUBSTITUTE( e,"u", "",1)
VAR i = SUBSTITUTE( u, "i","",1)
VAR o = SUBSTITUTE( i,"o", "",1)

var l = LEN('Table'[SearchText])-LEN(o)
RETURN
SWITCH(
    TRUE(),
    l>=3 ,3,
l>=2,2,
l>=1,1,
0
)

     

     

     

     

    Regards,
    Harsh Nathani
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Anonymous - You know, if it really is just three words you are trying to find, you could do this:

     

    Column 1 = IF(FIND("when",[Column],,0)>0,1,0)

    Column 2 = IF(FIND("then",[Column],,0)>0,1,0)

    Column 3 = IF(FIND("given",[Column],,0)>0,1,0)

    Column 4 = [Column 1] + [Column 2] + [Column 3]

     

     

15 Replies

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

    Anonymous - Try this:

     

    Column = 
    VAR __Vowels = { "a", "e", "i", "o", "u" }
    VAR __Table = SELECTCOLUMNS(ADDCOLUMNS(GENERATESERIES(1,LEN([SearchText]),1),"Letter",MID([SearchText],[Value],1)),"Letter",[Letter])
    VAR __Intersect = INTERSECT(__Table,__Vowels)
RETURN
    COUNTROWS(DISTINCT(__Intersect))

    PBIX is attached below signature. Table 6.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Greg_Deckler 

      Hi Greg, I tried this in my table, but for all rows it is giving me Blank() .

      Basically I am searching the below 3 keywords from my JIRA ticket's Acceptance Criteria.

      "given", "when" , "then".

      Is this case sensitive? For me it can be "given" or "Given" or "GIVEN"

      Where is it going wrong?

       

      AC Conformance =
      VAR __ACtext = { "given", "when", "then" }
      VAR __Table = SELECTCOLUMNS(ADDCOLUMNS(GENERATESERIES(1,LEN([Acceptance Criteria]),1),
      "Letter",MID([Acceptance Criteria],[Value],1)),"Letter",[Letter])
      VAR __Intersect = INTERSECT(__Table,__ACtext)
      RETURN
      COUNTROWS(DISTINCT(__Intersect))
       
      TIA
      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Anonymous - Can you give me an example of your actual data (sample representative) Obviously there are multiple words involved so you will need to change how I was doing the __Table creation.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous ,

     

     

    Create a Calculated Column

     

    Count = 
VAR a =SUBSTITUTE('Table'[SearchText], "a", "",1)
VAR e = SUBSTITUTE( a, "e", "",1)
VAR u = SUBSTITUTE( e,"u", "",1)
VAR i = SUBSTITUTE( u, "i","",1)
VAR o = SUBSTITUTE( i,"o", "",1)

var l = LEN('Table'[SearchText])-LEN(o)
RETURN
SWITCH(
    TRUE(),
    l>=3 ,3,
l>=2,2,
l>=1,1,
0
)

     

     

     

     

    Regards,
    Harsh Nathani
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous 

      Hi Harsh.

      Basically I am searching the below 3 keywords from my JIRA ticket's Acceptance Criteria.

      "given", "when" , "then"

      So how do I make it case sensitive?

      For me it can be "GIVEN", "Given","given" from the Acceptance Criteria field.

      I wrote this DAX but it is just giving me value (0,1,2,3) randomly .

      Not actually matching the condition.

       

      Count =
      VAR given =SUBSTITUTE('Requirements'[Acceptance Criteria], "Given", "",1)
      VAR whenn = SUBSTITUTE( "Given", "When", "",1)
      VAR thenn = SUBSTITUTE( "When","Then", "",1)
       
      var cnt = LEN('Requirements'[Acceptance Criteria])-LEN(thenn)
      RETURN
      SWITCH(
      TRUE(),
      cnt>=3 ,3,
      cnt>=2,2,
      cnt>=1,1,
      0
      )
       
      TIA
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ,

         

        Use the LOWER() function

         

        https://dax.guide/lower/

         

         

        Regards,
        Harsh Nathani
        Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)