Check if Column contains string with iterator?

[Jack007]

I need to verify if a column has certain strings in it. Currently, I am using the code If(containstring(columnname, "X"), "desired", "none").

However, the list of values for X is very large. Is there a method to make DAX iterate through each value of X?

[v-tianyich-msft · Accepted solution]

Hi @Jack007 ,

Power BI Dax does not support iterate operations on the data.

However, you can use the M language to filter specific strings for this purpose.

I created a simple sample:

  

First, you can create a parameter for your specific string like this:

Then use this M script:

= Table.SelectRows(each Text.Contains([Large Value], #"Check X")) 

Result shown below:

Perhaps this will work, and if you have any questions, please provide me with more information to make sure we can better solve the problem for you!

An attachment for your reference. Hope it helps.

Best regards,

Community Support Team_ Scott Chang

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

[123abc]

In Power BI DAX, you can't directly iterate through a list of values within a single calculated column formula. DAX is a language designed for creating calculated columns and measures, and it doesn't have native support for traditional iterative constructs like loops.

However, you can achieve the desired result by using functions that work with tables, such as FILTER and SUMMARIZE, to filter and manipulate your data. Here's a general approach to check if a column contains any of the values in a large list:

1. Create a table containing the list of values you want to check (let's call it "ValuesTable"). This table should have a column, let's call it "ValueToCheck."

2. Use DAX functions like FILTER and COUNTROWS to create a calculated column or measure that checks if the column contains any of the values from "ValuesTable."

Here's an example of how you can create a calculated column to check if a column named "ColumnName" contains any values from "ValuesTable":

ContainsValue =
IF(
COUNTROWS(
FILTER(
ValuesTable,
CONTAINSSTRING([ColumnName], ValuesTable[ValueToCheck])
)
) > 0,
"desired",
"none"
)

In this example, the FILTER function is used to create a filtered table that contains only rows where the "ValueToCheck" from "ValuesTable" is found in the "ColumnName." The COUNTROWS function then checks the number of rows in the filtered table. If it's greater than 0, it means at least one value from "ValuesTable" is found in "ColumnName," so "desired" is returned; otherwise, "none" is returned.

Keep in mind that using this approach can be resource-intensive, especially if you have a large dataset or a large list of values in "ValuesTable." You may want to optimize your data model and queries accordingly for performance reasons.

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