Search by Keyword in another table and multiple groupings

[avsparry]

Hi 

I am looking to search a table column (text) with keywords from another table and link it to that new table, essentially creating useful groupings from the key words. I have found several useful examples in this forum but none quite get to the bottom of my issue. I will use an example from the community.

Keyword_FACT

Keyword	Type
module	content
seminar	content
class	content
lecture	content
lesson	content
academic	staff
administrators	staff
staff	staff
lecturers	staff

Reference_FACT

Gender	Department	Comment
M	Classics	Lectures and lessons are really good.
M	Biology
M	Classics	I love the staff.
F	Classics	I had a fun time.
M	Physics	Academics are great
F	Biology	Enjoyable lectures
M	Classics	The staff give great seminars

usind DAX

Keyword = 
CONCATENATEX(
    'Keywords_FACT',
    IF(
        SEARCH('Keywords_FACT'[Keyword], 'Reference_FACT'[Comment],,999) <> 999,
        'Keywords_FACT'[Keyword] & " ",
        ""
        )
)

and

Type = 
CONCATENATEX(
    'Keywords_FACT',
    IF(
        SEARCH('Keywords_FACT'[Keyword], 'Reference_FACT'[Comment],,999) <> 999,
        'Keywords_FACT'[Type] & " ",
        ""
        )
)

results in

 GenderDepartment CommentKeywordType

Gender	Department	Comment	Keyword	Type
M	Classics	Lectures and lessons are really good.	lecture lesson	content content
M	Biology
M	Classics	I love the staff.	staff	staff
F	Classics	I had a fun time.
M	Physics	Academics are great	academic	staff
F	Biology	Enjoyable lectures	lecture	content
M	Classics	The staff give great seminars	seminar staff	content staff

So I can now use the Type column to filter my data however, if I have multiple keywords in different "types" i become unstuck (eg, final row in the example above). I don't want to create extra rows (i don't want to duplicate any data). 

I would like to filter by content or staff and have the final row available in both

If anyone can think of a useful solution would be great.

(as an aside I think it could be useful to convert the DAX into power query if that could be done as well)

[v-alq-msft · Accepted solution]

Hi, @avsparry 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

You may create a measure as below.

Visual Control = 
var x = 
COUNTROWS(
    FILTER(
        DISTINCT('Table'[Type]),
        CONTAINSSTRINGEXACT(MAX(Reference_FACT[Type]),[Type])
    )
)
return
IF(
    ISFILTERED('Table'[Type]),
    IF(
        x>0,
        1,0
    ),
    1
)

Finally you need to put the measure in the visual level filter to display the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@avsparry here you go, solution attached. I think there are a few things where I can improve but for now, it is ok. I think I have to spend more time working with List.Intersect for a more elegant solution.

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[avsparry]

@parry2k is there anyway to expand this to search in two columns?

[avsparry]

Just another comment. I have tried using this solution on my actual data set (which is much larger) and it is significantly slower than using DAX. Do you have this issue also?

I'm afraid I won't be able to send you this data set

[avsparry]

Hi @parry2k 

This works for me and sorts out moving the issue to power query, have you any advice on how to filter by type now we have it in this format?

Cheers

ps I like this solution, i'll try and get my head around all the bits, for anyone who doesn't want to download the pbix, you need to create this function and then use in addcolumn on your data table.

(searchText as text,   ignoreCase) =>
let
    SplitSearchTextToList = Text.Split(searchText," "),  //split search text by space
    searchList = Table.Column(Keywords_FACT, "Keyword"), //get list of keywords
    typeList = Table.Column(Keywords_FACT,"Type"),       //get list of keywords type

    SearchPos = List.Transform(SplitSearchTextToList,each 
    List.PositionOf( searchList, _, 0,(searchText,searchValue) => let newsearchText = if ignoreCase then Text.Lower(searchText) else searchText, newsearchValue = if ignoreCase then Text.Lower(searchValue) else searchValue  in Text.Contains(newsearchValue,newsearchText))), //search each search word in the keyword list and get the list of search position

    RemovePos = List.RemoveItems(SearchPos,{-1}), //remove not found search position 
    GetKeywords = Text.Combine(List.Transform(RemovePos,each searchList{_}),","), //get list of keywords based on the search position
    GetType = Text.Combine(List.Transform(RemovePos,each typeList{_}),","), //get list of type based on the saarch position
    #"Keywords and Type" = [Keywords=GetKeywords,Type=GetType] //create a record of keyword and type
in
    #"Keywords and Type"

[parry2k]

@avsparry can you also share the excel file you used in pbix since I will be doing transformation in PQ and need  data file.

[avsparry]

here you go https://www.dropbox.com/s/twug84pb94pzrgi/src.zip?dl=0

[parry2k]

@avsparry I think it will be easy to do in PQ than DAX and I have to go back and look at one of my solutions where I did a similar thing. Can you put this sample data in a pbix file and share it using One drive/google drive.

[avsparry]

Hi @parry2k 

thanks for having a look at this, here is a link to the example https://www.dropbox.com/s/qgjslguu4v1bgsl/communityexample.pbix?dl=0

I have been trying to implement something in PQ but I'm just not good enough at it. Really interested to see your solution.