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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Dayna
Helper V
Helper V

Conditional formatting on a field based on field length

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 CodeBarcode 1Barcode 2
X404719600638214047196006500
Y404719600638714047196006510
Z40471960063894047196006520

 

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

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

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.

vcgaomsft_1-1699950451306.png
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.

vcgaomsft_0-1699950237211.png

vcgaomsft_2-1699950590524.png

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

View solution in original post

4 REPLIES 4
Dayna
Helper V
Helper V

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! 

v-cgao-msft
Community Support
Community Support

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.

vcgaomsft_1-1699950451306.png
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.

vcgaomsft_0-1699950237211.png

vcgaomsft_2-1699950590524.png

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
Helper V
Helper V

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
Super User
Super User

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:
audreygerred_1-1699636936339.png

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

audreygerred_7-1699637317224.png

 


 

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:

audreygerred_8-1699637339226.png

 


 

Viola! You have your coloring:

audreygerred_6-1699637296671.png

 

audreygerred_5-1699637275239.png

 

 

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors