Forum Discussion

Andy83's avatar
Andy83
Frequent Visitor
2 years ago
Solved

CONTAINSSTRING in-conjunction with LookUpValue

Hello all,    My requirement: I am trying to search for specific words within a column but I am using a Sharepoint list as a data source.    CONTAINSSTRING(<within_text>, <find_text>)   Is it p...
Need Help
  • MNedix's avatar
    MNedix
    2 years ago

    Hi,

    Some assumptions to be made first:

    1. I assume the body of the text-columns "live" in a file (which you then import it via Power Query). Let's call this table Statement and the column Description

    2. You also have your own means to bring in the Sharepoint list as a query in Power BI. We will call this table SP-List and the column Terms

     

    What you can then do is the following - create a new column in the Statement table:

     

     

    Key = 
VAR _result = CONCATENATEX(SP-List,
IF(SEARCH(FIRSTNONBLANK(SP-List[Terms],1),Statement[Description],,0)<>0,SP-List[Terms],""))
RETURN
_result

     

     

    This will return the found term or it will be blank if nothing was found. You can manipulate this by doing:

     

    ...
RETURN
IF(ISBLANK(_result),"False","True")

     

    Once you update the Sharepoint list with a new term and bring it in Power BI then with a simple Refresh the new Term will be included in the calculation.

     

    If this solved your problem then please mark it as the solution.

  • MNedix's avatar
    MNedix
    2 years ago

    It's fairly simple (if I understand the addition correctly). Let's say you add another column to the Terms and call it Type; this will act as a family/category for the terms. In the table where you created the Key, you create a new column as below:

    Key_Type = LOOKUPVALUE(Terms[Type],Terms[Terms],Claims[Key])

     

    You then use this column as a filter (see screenshots)

     

     

Andy83's avatar
Andy83
Frequent Visitor
2 years ago

Hi, 

I was just wondering  if it possible to Filter the SP-List by adding a column called "Type" then incorporating this FILTER function within your piece of Dax about so you can just search for the specific terms allocated to a specific type. Hope this makes sense. Thank you.

MNedix's avatar
MNedix
Solution Sage
2 years ago

It's fairly simple (if I understand the addition correctly). Let's say you add another column to the Terms and call it Type; this will act as a family/category for the terms. In the table where you created the Key, you create a new column as below:

Key_Type = LOOKUPVALUE(Terms[Type],Terms[Terms],Claims[Key])

 

You then use this column as a filter (see screenshots)

 

 

  • Andy83's avatar
    Andy83
    Frequent Visitor
    2 years ago

    Thanks so much for your help with this. Much appreciated. 👍