Forum Discussion
CONTAINSSTRING in-conjunction with LookUpValue
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 _resultThis 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.
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)
Thanks for your reply. I am trying to search for specific words within a column. For example, "lunch","parking","meal","meals","car". I am currently searching for this terms but they are hardcoded in using the SEARCH function. Please note this column is a text column so the keywords aren't necessarily on their own. This is returning the amount of characters to that specific string. I have then used a IF function that if its > 0 then set the result to "true". I don't want to hardcode the searchable keywords into the Power Bi. I need the user to input the keywords into a Sharepoint list which will then search the column "reason for claim" for those specific words. I've started using the CONTAINSTRING function. Hope this makes sense. Thanks again for your help.
Solution SageHi,
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.
Thank you for your help. Apologies for the late reply. 👍
Hello again,
If I wanted to enhance this further would it be possible to search by type. If I had a "Type" column within my "SP-List" and the types where defined as "Lunch", "Parking", "Internet" etc. Then these terms (term 1, term 2, term 3) could be allocated a "type". Thank you again for your help.
- MNedix
Hi,
I don't think I understand what you're saying. Could you please be more specific? Can you put the outcome in a table or drawing?
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.