How to filter Power BI table using list of keywords (in a column in other table)

2
votes

I have a big data table with a column called Account Name. In another table (Accounts) I have a column of Account Keywords that contains parts of full account names. I want to filter the big data by column 'Account Name' using the list of keywords in the 'Acount Keywords' list.

I've created a new measure in the big data table called 'Filter Accounts' with the following DAX:

FILTER ACCOUNTS = contains(Accounts,Accounts[Account Keyword],Big_Data[Account Name])

But the "contains" function works on exact matches. I want it to return true if the 'Account Keyword' is found within any part of the 'Account Name' field. I also want it to ignore case.

The DAX statement results in TRUE only for exact matches. How do I modify my DAX to achieve the desired non-exact matches?

1
powerbidax

1 Answers

2
votes

DAX has two functions for text contains matching, CONTAINSSTRING and CONTAINSSTRINGEXACT, where the latter is case-sensitive but the former is not.

You can find how many keywords match an Account Name by writing a calculated column like this on the Big_Data table:

Keyword Matches =
COUNTROWS (
    FILTER (
        Accounts,
        CONTAINSSTRING ( Big_Data[Account Name], Accounts[Account Keyword] )
    )
)

To get a TRUE or FALSE output instead of a count, simply append > 0 to see if the count is a positive value.

Match Exists =
COUNTROWS (
    FILTER (
        Accounts,
        CONTAINSSTRING ( Big_Data[Account Name], Accounts[Account Keyword] )
    )
) > 0