Conditional formatting on a field based on field length

[Dayna]

Hello All,

I suspect this is fairly easy, but as I'm very new to conditional formatting within PowerBI, I'm struggling a bit... 

I have a table of products, and they have barcodes against them. For example:

Product Code	Barcode 1	Barcode 2
X	4047196006382	14047196006500
Y	4047196006387	14047196006510
Z	4047196006389	4047196006520

I also have a table that states what length the field should be, i.e. Barcode 1 should be 13 characters, whereas Barcode 2 should be 14. It changes per customer, so it needs to lookup to the field value.

I'd like to do some conditional formatting on Barcode 1 and Barcode 2, so if the length of the barcode matches what it should be, then it's green, else red. How would I do this?

I thought about putting the lookup value (i.e. BarcodeRequiredLength) into a measure and using that, but it's not obvious on how I'd apply the formatting correctly. 

Can someone assist, please?

Many thanks,

Dayna

[Dayna]

Wow - thanks all, that's really useful! I'll give it a go and see how I get on. Looking forward to using Conditional Formatting more in my reports, thanks again! 

[Anonymous · Accepted solution]

Hi @Dayna ,

My idea is to create a what-if parameter, get its value and save it as a variable to be passed to the code in the conditional format.

Use parameters to visualize variables - Power BI | Microsoft Learn
Then create 2 measures for conditional format:

Measure 1 = 
VAR _len = MAX('Lengths'[Lengths])
VAR _result = IF(LEN(MAX('Table'[Barcode 1]))=_len,"Green","Red")
RETURN
_result

Measure 2 = 
VAR _len = MAX('Lengths'[Lengths])
VAR _result = IF(LEN(MAX('Table'[Barcode 2]))=_len,"Green","Red")
RETURN
_result

When the length parameter is changed, the condition format also changes.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Dayna]

Hi @audreygerred 

Thank you for your detailed response! To throw a spanner in, the number to validate won't always be 13 or 14, it depends on the customer, but I do have the value for the customer for each barcode to what it should be (i.e. Barcode 1 for Customer X might be 13, whereas Barcode 1 for Customer Y might be 14). How do you pass through dynamic data into the conditional formatting, or must you always enter a static value for it to validate and colourcode against?

Many thanks!

[audreygerred]

Hello! I created two measures - one for length of barcode1 and one for length of barcode2:

Len Barcode1 = LEN(MIN('Table (2)'[Barcode1]))

Len Barcode2 = LEN(MIN('Table (2)'[Barcode2]))

Next, I made my table (you do not need to have the measures in the table for this to work - I just have them in so you can see what the lengths are and that the conditional formatting is working). Right click on your field for barcode1 in the well of the visualization pane and click Conditional formatting:

From here you can select what type you want (I chose background color). Fill in the pop-up that appears:

Basically - if the length of barcode 1 is not 13, I want it to be red. Repeat for Barcode2, but do it so it is anything other than 14, like below:

Viola! You have your coloring: