我有一个大数据表,其中有一列名为 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更多推荐
如何使用关键字列表过滤 Power BI 表(在其他表的列中)
发布评论