Forum Discussion
Pattern Match DAX Column
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!
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 AddExpectedValueOutput:
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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
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
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.
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
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.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 AddExpectedValueOutput:
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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
