Conditionals Help

[swalz]

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

[Anonymous]

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

[rajendraongole1 · Accepted solution]

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.

[Ritaf1983]

Hi @swalz 
What is the logic of the conditions of the wanted coloring?