Check for values from list in a string from a measure

user_12256122 0
Viewed 114

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

enter image description here .

The result should look as follow

enter image description here.

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.

Table2 list

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

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 |

Example of a model in the link:

https://drive.google.com/drive/folders/1QVaPfMHkY_QQQmcUrBgTLNCDwmbuwq98?usp=sharing

powerbi
2 Answers

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

If you are comfortable with Power Query, you can create a new table using this below Advanced Query code-

let
    Source = Table_1,
    #"Trimmed Text" = Table.TransformColumns(Source,{{"Calculated Column", Text.Trim, type text}}),
    #"Sorted Rows" = Table.Sort(#"Trimmed Text",{{"Calculated Column", Order.Ascending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Calculated Column"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Calculated Column"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Reordered Columns", {{"Calculated Column", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Calculated Column"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Calculated Column", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Calculated Column"}, Table2, {"List  "}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"List  "}, {"Table2.List  "}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Table2",{{"Index", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows1",{{"Table2.List  ", "Table2.List"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Index"}, {{"AllData", each _, type table [Index=number, Calculated   Column=nullable text, #"Table2.List  "=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [AllData][Calculated Column]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Values", "Custom.1", each [AllData][Table2.List]),
    #"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    #"Added Custom2" = Table.AddColumn(#"Extracted Values1", "Custom.2", each if Text.Length([Custom.1]) = 0 then false else true),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Index", "AllData", "Custom.1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Calculated Column"}, {"Custom.2", "Is_present"}})
in
    #"Renamed Columns1"

Output will be as below-

enter image description here

Now you can join your Table_1 and Table_1_new using Calculated Column and import Is_present from the new table for your further purpose.

user_36523450
Related