topic Re: How to express this SQL expression: Table.[ACCOUNTNO] not like '%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%' in DAX Commands and Tips

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

JustinDoh1 — Fri, 08 Jul 2022 21:01:00 GMT

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.

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

amitchandak — Sat, 09 Jul 2022 07:36:06 GMT

JustinDoh1 , This seem file, where are using this like

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

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

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

JustinDoh1 — Sun, 10 Jul 2022 05:15:47 GMT

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

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

CNENFRNL — Sun, 10 Jul 2022 20:38:08 GMT

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

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

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

JustinDoh1 — Sun, 10 Jul 2022 21:50:39 GMT

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

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

JustinDoh1 — Mon, 11 Jul 2022 20:39:34 GMT

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.

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

JustinDoh1 — Tue, 12 Jul 2022 18:20:51 GMT

CNENFRNL, amitchandak

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