Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Format Check

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!

 

1 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
 
Screenshot_5.jpg

View solution in original post

6 REPLIES 6
luapdoniv
Resolver II
Resolver II

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

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
Not applicable

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

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
 
Screenshot_5.jpg
Anonymous
Not applicable

Thank you!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.