Alternative syntax to CONTAINSSTRING using Direct Connect to SSAS

[cklopper]

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

[v-yingjl · Accepted solution]

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

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.

[Anonymous]

@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]

@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... 😞

[amitchandak]

@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