Forum Discussion

Kan1shk's avatar
Kan1shk
Regular Visitor
4 years ago
Solved

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

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 whateve...
Need Help
Show and Tell
Tips and Tricks
  • Alex_T's avatar
    Alex_T
    4 years ago

    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

     

Alex_T's avatar
Alex_T
Frequent Visitor
4 years ago

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 

Hope this helps

  • Alex_T's avatar
    Alex_T
    Frequent Visitor
    4 years ago

    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