如何使用关键字列表过滤 Power BI 表(在其他表的列中)

本文介绍了如何使用关键字列表过滤 Power BI 表(在其他表的列中)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个大数据表，其中有一列名为 Account Name.在另一个表(帐户)中，我有一列帐户关键字，其中包含部分完整帐户名称.我想使用帐户关键字"列表中的关键字列表按帐户名称"列过滤大数据.

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.

我使用以下 DAX 在名为过滤帐户"的大数据表中创建了一个新度量:

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

但包含"功能适用于完全匹配.如果在帐户名称"字段的任何部分中找到帐户关键字"，我希望它返回 true.我也希望它忽略大小写.

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.

DAX 语句仅对完全匹配结果为 TRUE.如何修改我的 DAX 以实现所需的非精确匹配?

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

推荐答案

DAX有两个函数用于文本包含匹配，CONTAINSSTRING和CONTAINSSTRINGEXACT，后者是case-敏感，但前者不敏感.

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

您可以通过在 Big_Data 表中写入这样的计算列来查找有多少关键字与 Account Name 匹配:

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

要获得 TRUE 或 FALSE 输出而不是计数，只需附加 >0 查看计数是否为正值.

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