从度量的字符串中检查列表中的值 [英] Check for values from list in a string from a measure
问题描述
我在 table_1 中有一个计算列，它是一个度量的结果，它返回一个包含多个值的字符串.这些值中的每一个都代表一个故障代码，其格式为 DI 后跟 4 位数字.现在我有一个包含故障代码列表的第二个表(table_2)，其中一些故障代码可以在第一个表的计算列的字符串中找到.我正在寻找的是一种度量，如果表 1 中计算列中的任何行的故障代码不在表 2 的故障代码列表中，则该度量将返回真"语句.此外，如果 table1 中计算列的字符串中的所有代码都存在于 table_2 的列表中，则将返回False"语句.请看一个简化的例子
I have a Calculated column in table_1 which is a result of a measure that returns a string with several values in it. Each of these values represents a fault code and its format is DI followed by 4 digits. Now I have a second table (table_2) that has a list of faults codes, some of those fault codes can be found in the string of the calculated column from the 1st table. What I'm looking for is a measure that will return a 'True' statement if any row from the calculated column in the table1 has a fault code that is not on the list of fault codes from table2. Additionally will return a 'False' statement in case of all codes from the string of calculated column in table1 are present on the list of table_2. Please see a simplified example
.
结果应该如下所示
.
到目前为止，我已经设法检查是否只有表 1 的计算列中字符串的第一个值满足该条件.不幸的是，我无法将其应用于完整的字符串，请有人支持.
So far I've managed to check if only the first value from the string in the calculated column of table 1 fulfils that condition. Unfortunately, I'm not able to apply it to the full string, Could someone support, please.
表2列表
| List |
|--------|
| DI0001 |
| DI0002 |
| DI0003 |
| DI0004 |
| DI0005 |
| DI0006 |
| DI0007 |
| DI0008 |
| DI0009 |
| DI0011 |
| DI0013 |
| DI0015 |
| DI0105 |
| DI0107 |
| DI0108 |
| DI0211 |
| DI0212 |
| DI0505 |
| DI0806 |
| DI0907 |
| DI1113 |
| DI1212 |
| DI1504 |
| DI1505 |
| DI1601 |
| DI1602 |
| DI1603 |
| DI1604 |
| DI1605 |
| DI1606 |
| DI1607 |
| DI1608 |
| DI1609 |
| DI1610 |
| DI1611 |
| DI1612 |
| DI1613 |
| DI1614 |
| DI1615 |
| DI1616 |
| DI1617 |
| DI1618 |
| DI1701 |
| DI1702 |
| DI1703 |
| DI1704 |
| DI1705 |
| DI1706 |
| DI1707 |
| DI1708 |
| DI1801 |
| DI1802 |
| DI1803 |
| DI1804 |
| DI1901 |
| DI1902 |
| DI1903 |
| DI1904 |
| DI1905 |
| DI2601 |
| DI9901 |
| DI9902 |
| DI9903 |
| DI9904 |
| DI9905 |
| DI9906 |
| DI9907 |
| DI9908 |
| DI9909 |
| DI9910 |
| DI9911 |
| DI9912 |
| DI9913 |
这里是table1和计算列
Here is table1 and the calculated column
| Calculated Column |
|-----------------------------------------------------------|
| DI0501 DI1604 DI1605 |
| DI1604 DI1605 |
| DI1604 DI1605 |
| DI1605 |
| DI1604 DI1605 DI0105 |
| DI1604 DI1605 |
| DI0105 DI1604 DI1605 |
| DI1605 DI1604 |
| DI1604 DI1605 DI0105 DI1604 DI1604 DI1604 DI1604 DI1604 |
| DI1604 DI1605 |
| DI1605 |
| DI1604 DI1605 |
| DI1605 |
| DI1604 DI1605 DI0105 |
| DI0105 DI1604 DI1605 |
| DI1604 DI1605 |
| DI1010 DI1604 DI1605 DI0105 |
| DI1604 DI1605 DI0105 |
| DI1604 DI1605 DI1604 DI1604 DI1604 DI1604 DI1604 |
| DI1604 DI1605 |
| DI1604 DI1605 |
| DI1604 DI1605 DI0105 DI1604 DI1604 DI1604 DI1604 DI1604 |
| DI0204 |
| DI1015 DI0105 DI1604 DI1605 |
| DI1604 DI1605 DI0105 |
| DI1604 DI1605 DI0105 |
| DI1604 DI1605 DI0105 DI1604 DI1604 DI1604 DI1604 DI1604 |
| DI1102 DI1605 |
| DI1605 |
| DI1604 DI1604 DI1605 |
| DI1604 DI1605 DI1604 |
| DI1605 DI1604 DI1010 |
| DI0107 |
| DI0105 DI1604 DI1605 |
| DI1604 DI0204 |
| DI0105 DI1604 DI1605 |
| DI1605 |
| DI1604 DI1605 DI0105 DI1604 DI1604 DI1604 DI1604 DI1604 |
链接中的模型示例:
https://drive.google.com/drive/folders/1QVaPfMHkY_QQQmcUrBgTLNCDwmbuwq98?usp=分享
推荐答案
这是修改后的 DAX Measure
This is the modified DAX Measure
_Measure =
VAR _1 =
FILTER (
ADDCOLUMNS (
Table_1,
"new", SUBSTITUTE ( Table_1[Calculated column], " ", "|" )
),
[new] <> BLANK ()
)
VAR _2 =
GENERATE (
_1,
ADDCOLUMNS (
GENERATESERIES ( 1, PATHLENGTH ( [new] ) ),
"_txt", TRIM ( PATHITEM ( [new], [Value], TEXT ) )
)
)
VAR _filt =
ADDCOLUMNS (
_2,
"score",
VAR _1 = [_txt]
VAR _2 =
CALCULATE ( MAX ( table_2[List] ), TREATAS ( { _1 }, table_2[List] ) )
RETURN
IF ( _2 = BLANK (), "x", _2 )
)
RETURN
IF (
COUNTX ( FILTER ( _filt, [score] = "x" ), [Calculated column] ) >= 1,
TRUE,
FALSE
)
这篇关于从度量的字符串中检查列表中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
