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