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

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

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

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 ，其中后者是区分大小写的-敏感，但前者不敏感.

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