Forum Discussion

JustinDoh1's avatar
JustinDoh1
Post Prodigy
4 years ago
Solved

How to express this SQL expression: Table.[ACCOUNTNO] not like '%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%'

I am trying to express bottom SQL statement in DAX: Table.[ACCOUNTNO] not like '%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%'   I tried with  not ( CONTAINSSTRING( 'Table'[ACCOUNTNO], "ABCDEFGHIJKLMNOPQRSTUVW...
Need Help DAX
  • CNENFRNL's avatar
    CNENFRNL
    4 years ago

    Table.[ACCOUNTNO] not like '%[A-Z]%' is enough,

     

     

     

    For fun only, a showcase of powerful Excel worksheet formula,

  • JustinDoh1's avatar
    JustinDoh1
    4 years ago

    CNENFRNL, amitchandak 

    I ended up finding solution by creating a separate Char table.

     

     

     

amitchandak's avatar
amitchandak
Super User
4 years ago

JustinDoh1 , This seem file, where are using this like

 

if (not ( CONTAINSSTRING( 'Table'[ACCOUNTNO], "ABCDEFGHIJKLMNOPQRSTUVWXYZ" )) , "A", "B")

 

or

 

calculate(sum(Table[Value]), filter(Table, not ( CONTAINSSTRING( 'Table'[ACCOUNTNO], "ABCDEFGHIJKLMNOPQRSTUVWXYZ" )) )

  • JustinDoh1's avatar
    JustinDoh1
    Post Prodigy
    4 years ago

    amitchandak 

    Thanks for help.

    Does bottom DAX expression look ok?

    Somehow, I am getting the same amount as the amount I validate result from SQL.

     

    Calculate(
    sum (Table[AMOUNT]),
    filter(
          Table,
              Table[CLASSID] = "100" &&
              (left('Table'[ACCOUNTNO],1) IN {"4", "5"}) &&
              not ( CONTAINSSTRING( 'Table'[ACCOUNTNO], "ABCDEFGHIJKLMNOPQRSTUVWXYZ" ))
    )