Forum Discussion

Dayna's avatar
Dayna
Icon for Helper V rankHelper V
2 years ago
Solved

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • audreygerred's avatar
    audreygerred
    Icon for Super User rankSuper User
    2 years ago

    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:

     

     

     

     

  • Dayna's avatar
    Dayna
    Icon for Helper V rankHelper V
    2 years ago

    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!

  • Dayna's avatar
    Dayna
    Icon for Helper V rankHelper V
    2 years ago

    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!