Forum Discussion

NewinPBi's avatar
NewinPBi
Frequent Visitor
6 years ago
Solved

valid letters or numbers

Hi,

I am validating a field, this field should only composed of letters(any and/or numbers, no any other character like -,_@,$%^& etc.

Valid letters/numbers:  ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’ 

Help appreciated and thanks in advance.

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, NewinPBi 

     

    Based your description, I created data to reproduce your scenario. The pbix file is attached in the end.

     

    Table:

     

    Valid:

     

    You may create a calculated column or a measure as below.

    Calculated colummn:
IsValid Column = 
var _letter = 'Table'[Validation]
var tab = 
ADDCOLUMNS(
    GENERATESERIES(1,LEN(_letter),1),
    "letter",
    RIGHT(LEFT(_letter,[Value]),1)
)

return
    IF(
        LEN(_letter)=
        COUNTROWS(
            FILTER(
                tab,
                [letter] in DISTINCT(Valid[Valid letter])
            )
        ),
        1,0
    )

Measure:
IsValid Measure = 
var _letter = MIN('Table'[Validation])
var tab = 
ADDCOLUMNS(
    GENERATESERIES(1,LEN(_letter),1),
    "letter",
    RIGHT(LEFT(_letter,[Value]),1)
)

return
IF(
    HASONEVALUE('Table'[Validation]),
    IF(
        LEN(_letter)=
        COUNTROWS(
            FILTER(
                tab,
                [letter] in DISTINCT(Valid[Valid letter])
            )
        ),
        1,0
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi NewinPBi ,

     

    You can try this custom column:

    let _validItems = {"0".."9"} & {"A".."Z"} in
if Text.Length(Text.Remove(Text.Upper([Text]), _validItems)) > 0 then 0 else 1

     

     

    • NewinPBi's avatar
      NewinPBi
      Frequent Visitor
      6 years ago

      This works as a custom column, but  I am trying to achieve this using an existing column name VALIDATION, I have other  IF conditions too on other fields

       

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, NewinPBi 

     

    Based your description, I created data to reproduce your scenario. The pbix file is attached in the end.

     

    Table:

     

    Valid:

     

    You may create a calculated column or a measure as below.

    Calculated colummn:
IsValid Column = 
var _letter = 'Table'[Validation]
var tab = 
ADDCOLUMNS(
    GENERATESERIES(1,LEN(_letter),1),
    "letter",
    RIGHT(LEFT(_letter,[Value]),1)
)

return
    IF(
        LEN(_letter)=
        COUNTROWS(
            FILTER(
                tab,
                [letter] in DISTINCT(Valid[Valid letter])
            )
        ),
        1,0
    )

Measure:
IsValid Measure = 
var _letter = MIN('Table'[Validation])
var tab = 
ADDCOLUMNS(
    GENERATESERIES(1,LEN(_letter),1),
    "letter",
    RIGHT(LEFT(_letter,[Value]),1)
)

return
IF(
    HASONEVALUE('Table'[Validation]),
    IF(
        LEN(_letter)=
        COUNTROWS(
            FILTER(
                tab,
                [letter] in DISTINCT(Valid[Valid letter])
            )
        ),
        1,0
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, NewinPBi 

     

    If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

     

    Best Regards

    Allan