Power BI - Referencing a column for a text search (with wildcards)

user_10887133 0
Viewed 1214

Let's say I have a large list of names in a single column. I want to see if the names have the letters "y" or "z" in them, with a calculated column that just tags the row as "Y" or "Z".

My question is not just about searching with wildcards in DAX. My main question is whether I can create the following table:

+-------+-------+
|   Y   |   Z   |
+-------+-------+
| "*y*" | "*z*" |
+-------+-------+

and then use the contents of that table in a DAX search (without creating any relationship).

In DAX-ish pseudocode,something like:


Column =
IF (
    FOR x in 'Letters'[Y]:
        SEARCH (
            x,
            Names[Name],
            1,
            0
        ),
        "Y",
        BLANK()
)

This is just a simplified example, in this case I would just create a DAX formula for Y and Z. In my real life situation, that second table will be pulled in from a constantly changing Excel spreadsheet, containing hundreds of wildcard queries...

Is this possible in Power BI?

dax powerbi
1 Answers

Let's say I have a table Names:

Name
----
the
quick
brown
fox
jumps
over
the
lazy
dog

And Letters table with two columns:

Y  Z
----
x  a
y  b
z  c

Then I can write a calculated column on my Names table that checks if any string in Letters[Y] is a substring of row in Names[Name]:

Y =
VAR TempTable =
    ADDCOLUMNS (
        Letters,
        "Match", IF ( CONTAINSSTRING ( Names[Name], Letters[Y] ), "Y" )
    )
RETURN
    MAXX ( TempTable, [Match] )

Let's look at the fox row for example. The TempTable for that row is

Y  Z  Match
-----------
x  a  Y     /*Since 'x' is in 'fox'*/
y  b
z  c

Taking the max of the Match column, we get "Y" if there are any matches and blank otherwise.

A Z column can be defined analogously.

The result is the following:

Result

user_7652260
Related