Power BI Custom Filter Creation with DAX or any other inputs?

[Kan1shk]

Hello Community! 🙂

How can one set a certain criteria which returns all the wrong format, i.e. which does not match the format like DE followed by 9 digits in Power BI columns? Basically whatever is not matching the defined format, should be displayed. Thank you in advance:)

[Kan1shk]

@Alex_T  Thank you so much. I was trying to solve the same, I think this should work..

[Alex_T]

Hi there, this isn't a complete answer but to start you off, you could write an M-query function that tests various components of a string and returns a flag to indicate if it's wrong. You can't do pattern matching so would have to use a combination of text and list functions to test various components, such as 

• text extraction 
• text membership 
• text transformation 
• list functions once the text is split into a list

Hope this helps

[Alex_T · Accepted solution]

Bit rushed earlier, here's an example

(_text as text) as logical =>
let

_text_asList = Text.ToList(_text),
_length = 8,
_invalid_chars = Text.ToList("{}[]/\,. ;:'"),
_letters = {"A".."Z"},
fnCheckRange = (_string, _start, _count) as logical =>

let

result = List.ContainsAll(
_letters,
Text.ToList(Text.Middle(_string,_start,_count))
)

in

result,

//__tests__
_not_uppercase = _text <> Text.Upper(_text),
_wrong_length = Text.Length(_text) <> _length,
_contains_invalid_chars = List.ContainsAny(_text_asList,_invalid_chars),
_not_validChars = not fnCheckRange(_text,0,2),
_test_outcome = List.AnyTrue({

_not_uppercase,
_wrong_length,
_contains_invalid_chars,
_not_validChars
})

in

_test_outcome