Pattern Match DAX Column

[trb7fh]

I'm needing to correct a DAX column but can't figure out how it's breaking to fix it.

We have a column for the pattern the table is expected to follow in the column "Current Flag Pattern". The first value determines the number of Key columns that should have a value, sequentially. The second value determines the number of Current Flag Field columns that should have a value, sequentially. So a value of "2&2" in the "Current Flag Pattern" column means the columns Key 1, Key 2, and Current Flag Field 1, and Current Flag Field 2 should have values while Key 3 - Key 10 and Current Flag Field 3 should all be null. The measure however is returning TRUE for all rows, regardless if the pattern is being followed or not.

I've searched Google and here and couldn't find a similar pattern to troubleshoot with. Even Copilot seems lost that it's not working. It's also possible this measure has become so complicated it needs to be rewritten entirely. I've uploaded a sample data set, PBIX, and text file with the current measure here. Any assistance would be appreciated!

[nilendraFabric]

Thanks @trb7fh  for getting back. 

lets give this a try

 you’ll need to modify your existing measure to correctly evaluate the pattern based on the “Current Flag Pattern” column. Here’s a suggested approach to rewrite the measure:
1. Parse the “Current Flag Pattern” column to extract the number of Key columns and Current Flag Field columns that should have values.
2. Use the SWITCH function to handle different pattern cases.
3. Utilize the AND function to check if all conditions are met for both Key columns and Current Flag Field columns.

Pattern Match =
VAR KeyCount = INT(LEFT(SELECTEDVALUE('Table'[Current Flag Pattern]), 1))
VAR FlagCount = INT(RIGHT(SELECTEDVALUE('Table'[Current Flag Pattern]), 1))
RETURN
SWITCH(
TRUE(),
KeyCount = 1 && FlagCount = 1,
AND(
NOT(ISBLANK('Table'[Key 1])),
ISBLANK('Table'[Key 2]),
ISBLANK('Table'[Key 3]),
NOT(ISBLANK('Table'[Current Flag Field 1])),
ISBLANK('Table'[Current Flag Field 2]),
ISBLANK('Table'[Current Flag Field 3])
),
KeyCount = 2 && FlagCount = 2,
AND(
NOT(ISBLANK('Table'[Key 1])),
NOT(ISBLANK('Table'[Key 2])),
ISBLANK('Table'[Key 3]),
NOT(ISBLANK('Table'[Current Flag Field 1])),
NOT(ISBLANK('Table'[Current Flag Field 2])),
ISBLANK('Table'[Current Flag Field 3])
),
// Add more cases as needed
FALSE // Default case if no pattern matches
)

if this helps please give kudos and accept the solution 

[trb7fh]

That returned an error of "Too many arguments were passed to the AND function. The maximum argument count for the function is 2."

Edit: this is one of the solutions I tried when I first was writing this measure and ran into the same error.

[Anonymous · Accepted solution]

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.

[trb7fh]

It should return TRUE if the pattern is not followed. In the example of line 1, it should only have values in Key 1 and Current Flag Fields 1-3, with the rest of the columns null. Because it has values in the Key 2 field, the expected result is TRUE.

We are using this for data verification. TRUE results mean the pattern is mismatched and the data will need to be cleaned.

Your measure does work however! I just need to switch the TRUE and FALSE outputs to match our standards for this project. Much appreciated!

[nilendraFabric]

Hello @trb7fh 

Is my understanding correct about the problem you are facing:

1. You have a column called “Current Flag Pattern” that specifies a pattern for other columns to follow.
2. The pattern is in the format “X&Y”, where:
• X represents the number of “Key” columns that should have values
• Y represents the number of “Current Flag Field” columns that should have values
3. For example, a pattern of “2&2” means:
• Key 1 and Key 2 should have values
• Key 3 to Key 10 should be null
• Current Flag Field 1 and Current Flag Field 2 should have values
• Current Flag Field 3 should be null
4. Your current DAX measure is returning TRUE for all rows, regardless of whether the pattern is actually being followed or not.

please confirm and we can work on DAX afterwards

[trb7fh]

Correct. I suspect the specificity of "2&2" meaning the first two Key and Current Flag Field columns, rather than just any two, are the culprit, but I haven't been able to figure out anything from that front so I won't rule out any other part of the measure being the issue.