Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JustinDoh1
Post Prodigy
Post Prodigy

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], "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.
 
2 ACCEPTED SOLUTIONS
CNENFRNL
Community Champion
Community Champion

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

 

CNENFRNL_1-1657484001792.png

 

CNENFRNL_2-1657485351317.png

 

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

CNENFRNL_3-1657485430129.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

@CNENFRNL@amitchandak 

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

JustinDoh1_0-1657649938898.png

JustinDoh1_1-1657649982313.png

JustinDoh1_2-1657650041647.png

 

 

 

View solution in original post

6 REPLIES 6
CNENFRNL
Community Champion
Community Champion

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

 

CNENFRNL_1-1657484001792.png

 

CNENFRNL_2-1657485351317.png

 

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

CNENFRNL_3-1657485430129.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

@CNENFRNL 

I have attached PBIX file here:

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

CONTAINSSTRING( [ACCOUNTNO], "ABCDEFGHIJKLMNOPQRSTUVWXYZ" )

 

JustinDoh1_0-1657571331687.png

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

JustinDoh1_1-1657571472779.png

 

$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.

 

@CNENFRNL@amitchandak 

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

JustinDoh1_0-1657649938898.png

JustinDoh1_1-1657649982313.png

JustinDoh1_2-1657650041647.png

 

 

 

@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
Super User
Super User

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

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors