Pattern Match DAX Column

Hi trb7fh 

Based on my testing, this seems to require the use of M function. You can try doing the following in Transform Data (Power Query).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xZHRCsIwDEV/ZfR5Qteq76FNYdB10qaCzLGv2P/bWScUFGGCPt2bEnJP0mFghIF2uq9cT1UMyGrWXGfOxVEmi93JtnoxxmhK6qK1WwQU6OmC4JNXYNFp8Gtt2pCecjXWmUiUSCIjiX8iybdXegx8kv2KaF8S8S+iP8qaeSgzZb5Ck2xAum+fVDny9gw2Li3kEScHHW7JfvXJ43gD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Target Table" = _t, #"Current Flag Pattern" = _t, #"Key 1" = _t, #"Key 2" = _t, #"Key 3" = _t, #"Key 4" = _t, #"Key 5" = _t, #"Key 6" = _t, #"Key 7" = _t, #"Key 8" = _t, #"Key 9" = _t, #"Key 10" = _t, #"Current Flag Field 1" = _t, #"Current Flag Field 2" = _t, #"Current Flag Field 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Target Table", type text}, {"Current Flag Pattern", type text}, {"Key 1", type text}, {"Key 2", type text}, {"Key 3", type text}, {"Key 4", type text}, {"Key 5", type text}, {"Key 6", type text}, {"Key 7", type text}, {"Key 8", type text}, {"Key 9", type text}, {"Key 10", type text}, {"Current Flag Field 1", type text}, {"Current Flag Field 2", type text}, {"Current Flag Field 3", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","NULL",null,Replacer.ReplaceValue,{"Key 1", "Key 2", "Key 3", "Key 4", "Key 5", "Key 6", "Key 7", "Key 8", "Key 9", "Key 10", "Current Flag Field 1", "Current Flag Field 2", "Current Flag Field 3"}),
    AddExpectedValue = Table.AddColumn(#"Replaced Value", "Expected Value", each 
        let
            Pattern = Text.Split([Current Flag Pattern], "&"),
            KeyCount = Number.FromText(Pattern{0}),
            FieldCount = Number.FromText(Pattern{1}),
            Keys = { [Key 1], [Key 2], [Key 3], [Key 4], [Key 5], [Key 6], [Key 7], [Key 8], [Key 9], [Key 10] },
            Fields = { [Current Flag Field 1], [Current Flag Field 2], [Current Flag Field 3] },
            KeysCorrect = List.Count(List.Select(List.FirstN(Keys, KeyCount), each _ <> null)) = KeyCount,
            KeysBlank = List.Count(List.Select(List.Skip(Keys, KeyCount), each _ = null)) = (10 - KeyCount),
            FieldsCorrect = List.Count(List.Select(List.FirstN(Fields, FieldCount), each _ <> null)) = FieldCount,
            FieldsBlank = List.Count(List.Select(List.Skip(Fields, FieldCount), each _ = null)) = (3 - FieldCount),
            AllKeysAndFieldsNull = List.AllTrue(List.Transform(Keys, each _ = null)) and List.AllTrue(List.Transform(Fields, each _ = null)),
            Result = if [Current Flag Pattern] = "0" then
                        AllKeysAndFieldsNull
                     else 
                        KeysCorrect and KeysBlank and FieldsCorrect and FieldsBlank
        in
            Result
    )
in
    AddExpectedValue

Output:

But what I need to confirm with you is that, according to my understanding, for example, the first line in the following screenshot requires key1, Current Flag Field 1 , Current Flag Field 2, Current Flag Field 3 to have a value, and the other columns to be empty, and returns True if this condition is met?

If I understand correctly, based on the example data in excel, the expected result should be FFFTT. Please feel free to correct me if I am wrong.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.