Power BI-引用列进行文本搜索(带通配符) [英] Power BI - Referencing a column for a text search (with wildcards)

问题描述

比方说，我在单列中列出了大量名称.我想查看名称中是否包含字母"y"或"z"，并带有计算所得的列，该列仅将行标记为"Y"或"Z".

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".

我的问题不只是关于在DAX中使用通配符进行搜索.我的主要问题是是否可以创建下表:

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*" |
+-------+-------+

，然后在DAX搜索中使用该表的内容(不创建任何关系).

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

在DAX类伪代码中，类似:

In DAX-ish pseudocode,something like:

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

这只是一个简化的示例，在这种情况下，我将为Y和Z创建一个DAX公式.在我的现实生活中，第二张表将从不断变化的Excel电子表格中提取，其中包含数百个通配符查询...

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...

在Power BI中有可能吗?

Is this possible in Power BI?

推荐答案

假设我有一张表名称:

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

和 Letters 表具有两列:

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

然后，我可以在我的 Names 表上写一个计算列，以检查 Letters [Y] 中的任何字符串是否为 Names [Name]中的行的子字符串] :

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] )

让我们看一下 fox 行.该行的 TempTable 是

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

取 Match 列的最大值，如果有匹配项，则得到"Y" ，否则为空.

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

可以类似地定义 Z 列.

结果如下:

这篇关于Power BI-引用列进行文本搜索(带通配符)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！