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.

     

     

     

CNENFRNL's avatar
CNENFRNL
Community Champion
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
Post Prodigy
4 years ago

CNENFRNL 

Thanks for help.

I was looking at the Pbix file, but I guess I am trying to figure out DAX from where I have left off (expression like: not ( CONTAINSSTRING( 'Table'[ACCOUNTNO], "ABCDEFGHIJKLMNOPQRSTUVWXYZ" ).

 

This is what I have so far.

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

 

I am still not getting the expected value (matching with my SQL's output from SQL query below):

 

select
sum ( [AMOUNT] )
from [dbo].[Table]
where [CLASSID] in ('100')
and left([ACCOUNTNO],1) in ('4', '5')
and [ACCOUNTNO] not like '%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%'