"Contains Number" with DAX Measure

[cn4422]

Hi,

I wanted to count all fields that contain a specific product number, in this case 894910000 (then I would create further measures for the other products, containing 894910001, 894910002, etc.)

This is the table: 

So the output should be: 534+105+68+1+270+1+1

I created the following measure:

Count product_selection Test Multiple Choice =

CALCULATE(COUNT('lead'[Product selection]),

SEARCH("894910000", 'lead'[Product selection], 1, 0)>0)

I used the above measure to search for "text" before and it worked, but maybe it does not work for numbers?

Thanks! 🙂

[Anonymous · Accepted solution]

Hi @cn4422 

Thank you very much DataNinja777 for your prompt reply. Allow me to add something.

I conducted a test for the question you raised.

It turns out that both SEARCH and CONTAINSSTRING work with text and numbers.

“NUMBER lead”

“TEXT lead”

However, please note that in the screenshot you provide, if you change the data type to a number because the data is too large, scientific notation is used. This can have an impact on your measure results.

“lead”

Therefore, I recommend that you use text-type data for your calculations.

Regards,

Nono Chen

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

[DataNinja777]

Hi @cn4422 ,

Since 'lead'[Product selection] is a text column, the original formula using Search should work for numbers, as Search is designed to handle text strings, including numeric strings within text. However, if the formula isn't working as expected, it's possible that other issues, such as extra spaces or unexpected characters in the text, might be affecting the search.

To confirm if the issue lies with formatting or the presence of extra characters, you could try using TRIM to remove any leading or trailing spaces, or alternatively, Containsstring, which might handle the search more intuitively for your case.

Here’s a more direct version using Containsstring for simplicity:

Count product_selection Test Multiple Choice =
CALCULATE(
    COUNT('lead'[Product selection]),
    CONTAINSSTRING('lead'[Product selection], "894910000")
)

This should work effectively for numeric strings within text columns. Let me know if this resolves the issue!

Best regards,

[cn4422]

Hi @DataNinja777 

thanks for your reply!

Thats really strange... actually I get the following error message:

"The data for this visualization was not found.
OLE DB or ODBC error: [Expression.Error] The expression could not be folded for the data source. Try a simpler expression."

[cn4422]

@Anonymous @DataNinja777 

Thanks again guys for your effort! 🤗

It just didn't work for me in this case but it wouldn't get into my mind why it wouldn't work, because your solutions were absolutely logical.

I tried lots of things and finally got it working with basically your formula plus adding an "*"-sign at the end of the string... 😊

Count product_selection Test Contain Multiple Choice =

CALCULATE(

    COUNT('lead'[Product selection]),

    CONTAINSSTRING('lead'[Product selection], "894910000*")

)