Filter values with specific format.

[StuartSmith]

I have a column that contains building codes and each entry should consist of 3 letters and 2 numbers, such as LON45.  Unfortunately, lots of the cells are blank or the incorrect format.  How can I create a filter to only display cells that dont match the 3 Letters & 2 Nums or even 5 Chars long.

Thanks in advance.

[v-shex-msft]

Hi @StuartSmith,

Please take a look at the following link to know more about Dax string comparison:

From SQL to DAX: String Comparison  

In addition, you can create a measure with expression include a condition to check current value, then you can use it on your visuals to filter not matched records. (I split text to char list and check the specific characters' Unicode to confirm if they meets your requirement, text: 97~122, number: 48~57)

Applying a measure filter in Power BI 

Formula:

IsMatch = 
VAR selected =
    SELECTEDVALUE ( 'Table'[Text] )
VAR charList =
    ADDCOLUMNS (
        GENERATESERIES ( 1, LEN ( selected ), 1 ),
        "Chararcter", IF (
            [Value] > 1,
            LEFT ( RIGHT ( selected, LEN ( selected ) - [Value] + 1 ), 1 ),
            LEFT ( selected, 1 )
        )
    )
VAR left3 =
    COUNTROWS (
        FILTER (
            charList,
            [Value] <= 3
                && UNICODE ( LOWER ( [Chararcter] ) ) IN GENERATESERIES ( 97, 122, 1 )
        )
    ) = 3
VAR right2 =
    COUNTROWS (
        FILTER (
            charList,
            [Value]
                >= COUNTROWS ( charList ) - 1
                && UNICODE ( LOWER ( [Chararcter] ) ) IN GENERATESERIES ( 48, 57, 1 )
        )
    ) = 2
RETURN
    IF ( LEN ( selected ) >= 5, IF ( left3 && right2, "Y", "N" ), "N" )

Expression result:

You can use the above formula on 'visual level filter' to filter 'N' result.

Regards,

Xiaoxin Sheng

[StuartSmith]

Thanks for the answer.  Can I ask a couple of questions about your answer.  If I use your code to create a measure it works, but not for a column.  Why is that?

Also, on the filtering for the "N" works and displays anything that doesnt meet the "XXX00" format...

But some of the "Y" results should be "No"...

Also, I was hoping to display the number of "N" in a card, but if I add the measue to the card, it doesnt allow me to filter.

Thanks in advance.

[v-shex-msft]

Hi @StuartSmith,

Can you please share some dummy data with a similar data structure and expected result? It should help us clarify these string formats and test to coding formula on them.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[amitchandak]

@StuartSmith , check these can help

https://docs.microsoft.com/en-us/powerquery-m/text-functions