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

[JustinDoh1]

I am trying to express bottom SQL statement in DAX:

Table.[ACCOUNTNO] not like '%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%'

I tried with 

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

but, it would not work.

This was based on this link in Stackoverflow:

When I initially searched online, I got this link (Russo's) , but I am not sure how I should approach.

Thank you for help.

[CNENFRNL · Accepted solution]

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

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

[CNENFRNL · Accepted solution]

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

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

[JustinDoh1]

@CNENFRNL 

I have attached PBIX file here:

So, I am not sure why it would not calculate this part correctly:

CONTAINSSTRING( [ACCOUNTNO], "ABCDEFGHIJKLMNOPQRSTUVWXYZ" )

If this DAX formula works, it should generate total of 8074.

$133.00
$12.00
$432.00
$589.00
$848.00
$374.00
$168.00
$846.00
$841.00
$773.00
$899.00
$1,086.00
$736.00
$337.00

Currently, it shows 0.

I am wondering if this is something to do with data type.

Currently, in PBI file, ACCOUNTNO is in "text" data type.

[JustinDoh1 · Accepted solution]

@CNENFRNL, @amitchandak 

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

[JustinDoh1]

@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]%'

[amitchandak]

@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]

@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" ))
)