Forum Discussion

swalz's avatar
swalz
New Member
1 year ago
Solved

Conditionals Help

Looking for some assistance with understanding how to apply conditionals based on value naming conventions. We have a standard naming convention that needs to be followed with several variations as follows:

 

X-XXXX

X-XXXX-X

X-XXXX-XXXX

X-XXXX-XXXX-X

X-XXXX-XXXX-XXXX

etc.

 

A quick look at the values I'm dealing with is shown below where green highlight is good, red is bad.

 

 

What are the best and easiest ways to achieve within PowerBI?

 

Thanks!

 

Steve

Need Help
Show and Tell
Tips and Tricks
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi swalz - you can achieve by creating a calculated column or measure in Power BI that applies a pattern-matching logic

     

     

    IsValidName =
    SWITCH(
        TRUE(),
        -- X-XXXX (Length = 6)
        LEN([NamingConvention]) = 6 && MID([NamingConvention], 2, 1) = "-", 1,
       
        -- X-XXXX-X (Length = 😎
        LEN([NamingConvention]) = 8 && MID([NamingConvention], 2, 1) = "-" && MID([NamingConvention], 7, 1) = "-", 1,
       
        -- X-XXXX-XXXX (Length = 11)
        LEN([NamingConvention]) = 11 && MID([NamingConvention], 2, 1) = "-" && MID([NamingConvention], 7, 1) = "-", 1,
       
        -- X-XXXX-XXXX-X (Length = 13)
        LEN([NamingConvention]) = 13 && MID([NamingConvention], 2, 1) = "-" && MID([NamingConvention], 7, 1) = "-" && MID([NamingConvention], 12, 1) = "-", 1,
       
        -- X-XXXX-XXXX-XXXX (Length = 16)
        LEN([NamingConvention]) = 16 && MID([NamingConvention], 2, 1) = "-" && MID([NamingConvention], 7, 1) = "-" && MID([NamingConvention], 12, 1) = "-", 1,
       
        -- If none of the patterns match
        0
    )
     
    i am using table chart in format pane to go the cell element apply the new column condition 1 for good and 0 for bad. i am using dark color you can choose your colors as per your request
     

     

     

     

    Hope this helps.

     

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi swalz - you can achieve by creating a calculated column or measure in Power BI that applies a pattern-matching logic

     

     

    IsValidName =
    SWITCH(
        TRUE(),
        -- X-XXXX (Length = 6)
        LEN([NamingConvention]) = 6 && MID([NamingConvention], 2, 1) = "-", 1,
       
        -- X-XXXX-X (Length = 😎
        LEN([NamingConvention]) = 8 && MID([NamingConvention], 2, 1) = "-" && MID([NamingConvention], 7, 1) = "-", 1,
       
        -- X-XXXX-XXXX (Length = 11)
        LEN([NamingConvention]) = 11 && MID([NamingConvention], 2, 1) = "-" && MID([NamingConvention], 7, 1) = "-", 1,
       
        -- X-XXXX-XXXX-X (Length = 13)
        LEN([NamingConvention]) = 13 && MID([NamingConvention], 2, 1) = "-" && MID([NamingConvention], 7, 1) = "-" && MID([NamingConvention], 12, 1) = "-", 1,
       
        -- X-XXXX-XXXX-XXXX (Length = 16)
        LEN([NamingConvention]) = 16 && MID([NamingConvention], 2, 1) = "-" && MID([NamingConvention], 7, 1) = "-" && MID([NamingConvention], 12, 1) = "-", 1,
       
        -- If none of the patterns match
        0
    )
     
    i am using table chart in format pane to go the cell element apply the new column condition 1 for good and 0 for bad. i am using dark color you can choose your colors as per your request
     

     

     

     

    Hope this helps.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, swalz 

    Thanks for Ritaf1983 and rajendraongole1 reply. You can refer to their reply, or you can try the following way.

    1. Add a new column to remove non-alphanumeric characters in Power Query

    Text.Select([Column1], {"A".."Z", "a".."z"})



    2. Write the following dax to create a calculate column

     

    Format = 
VAR NameLength =
    LEN ( [Name] )
VAR FirstChar =
    LEFT ( [Name], 1 )
VAR RemainingChars =
    MID ( [Name], 2, NameLength - 1 )
VAR FormattedRemainingChars =
    CONCATENATEX (
        GENERATESERIES ( 1, LEN ( RemainingChars ), 4 ),
        MID ( RemainingChars, [Value], 4 ),
        "-"
    )
RETURN
    FirstChar & "-" & FormattedRemainingChars

     

    Best Regards,
    Yang
    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