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 f...
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.

     

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

format.pbix29 KB