Format Check

[Anonymous]

Hello all,

I'm trying to create a check to ensure that users are entering the Billing Entity in the correct format, which is always 3 letters, dot, 4 numbers; ie BDA.5468

Is therea way to highlight any records where the billing entity does not match this format? I don't have access to the Edit Queries in this dataset, so would have to be a column.

Thanks!

[luapdoniv]

I would suggest, create a new view and filter out for that dimension to "Not contain" "."

[luapdoniv]

If you are expecting entire formats to change, could you share a sample source data, so that I can work it out and see. Thanks.

[Anonymous]

It varies quite a bit as I believe the field is free text, so the first three are correct and then the rest are examples of errors:

BDA.4856

GHS.2586

CPT.1854

GHE.4586_NAME_OF_COMPANY_UNDERSCORES

1238

LPT.8675 NAME OF COMPANY NO UNDERSCORES

[luapdoniv · Accepted solution]

Create 2 measures:

1. to see if the "." is in place: 

dotplace = find(".",'Table'[Column1],1,0)
2. to see if the length exceeds 4 after dot

PlacesAfterDot = if(len(mid('Table'[Column1],5,50))>4 || len(mid('Table'[Column1],5,50))<4,1,0)
then apply conditional formatting on the column to get the desired output.
You may need to write additional logics to suit your other possible combination requirements

[Anonymous]

Thank you!

[luapdoniv]

You are welcome and hope your problem got resolved. Let me know if you need any further help. Cheers!