Forum Discussion

cklopper's avatar
cklopper
Frequent Visitor
5 years ago
Solved

Alternative syntax to CONTAINSSTRING using Direct Connect to SSAS

What alternative DAX syntax can I use to CONTAINSSTRING when connecting to a tabular model using SSAS?

 

I want to define a measure that counts the number of accounts containing a specific string. I'm new to DAX, so forgive me if this is wrong, but something like this is what I wanted to do:

 

CALCULATE(Account[Total Count Account Codes], CONTAINSSTRING(Account, Account[RegionalAccountClass], "abc" ||
    'Account'[RegionalAccountClass] = "xyz"))
Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    cklopper , I think problem of placement of )

    Try like

    CALCULATE(Account[Total Count Account Codes], CONTAINSSTRING(Account[RegionalAccountClass],"abc" ) ||
    'Account'[RegionalAccountClass] = "xyz")

     

    or

    CALCULATE(Account[Total Count Account Codes],filter(Account, CONTAINSSTRING(Account[RegionalAccountClass],"abc" ) ||
    'Account'[RegionalAccountClass] = "xyz"))

    refer

    https://docs.microsoft.com/en-us/dax/containsstring-function-dax

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    cklopper , Alternative for CONTAINSTRING might be FIND.

    Meanwhile, you can use the following DAX:

     

    Original DAX:

    CALCULATE(Account[Total Count Account Codes],

    CONTAINSSTRING(Account[RegionalAccountClass],"abc" ) ||
    CONTAINSSTRING('Account'[RegionalAccountClass] = "xyz"))

     

    New DAX:

     

    CALCULATE(Account[Total Count Account Codes],
OR(
CONTAINSSTRING(Account[RegionalAccountClass],"abc" ),
CONTAINSSTRING('Account'[RegionalAccountClass], "xyz")
)

     

     

     

    • cklopper's avatar
      cklopper
      Frequent Visitor
      5 years ago

      Anonymous, amitchandak thanks for the suggestions. Unfortunately CONTAINSSTRING doesn't work because I'm connected directly to SQL Server Analysis Services. It's not available as a function and PBI Desktop won't recognise it... 😞

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    Hi cklopper ,

    Sorry for replying late.  Plese check the SSAS version you are running as the CONTAINSSTRING function only works from >= SSAS 2019 CTP2.3.

    You can refer this simliar issue:

    https://community.powerbi.com/t5/Desktop/CONTAINSSTRING-not-avaialble-when-Directly-Connected-to-TDM/td-p/770232

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.