Forum Discussion

JustinDoh1's avatar
JustinDoh1
Icon for Post Prodigy rankPost 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
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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's avatar
    JustinDoh1
    Icon for Post Prodigy rankPost Prodigy
    4 years ago

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