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

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

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

CNENFRNL 

I have attached PBIX file here:

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

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

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.