Create a Measure to check if any column text is contained

[jiandaoz]

Hi All,

I am trying to create a measure that will only display the rows containing text from the other table’s selected items. I build a very simple illustration as attached.

PowerBI File 

Say if I have two tables and I select both “apple” and “orange” in the first table, the Description table only displays rows with ID 1 and 2.

If I select all Fruit, the Description table only display rows with ID 1, 2, and 4.

If I select only pork, the Description table will show nothing.

[emjp]

My first question is why do you need to filter items that does not belong to the fruit category?

I propose the following approach:

1. The fruit list should only contain fruits or add a new column with the category, in order to avoid step 3
2. Add a custom column with the following power query expression:

= Table.AddColumn(#"Changed Type", "Custom", each List.Accumulate(

    List.Numbers(0, Table.RowCount(Table1)), 

    [Description], 

    (state, current) => 

       if Text.Contains (state, Table1[Fruit]{current}, Comparer.OrdinalIgnoreCase) then Table1[Fruit]{current} else state))

this expression searches if in the description there is any coincidence with the list of fruits, if there is, it returns the fruit, if not, there is no change.

3. To categorize the results, I add another column with the following expression:

= Table.AddColumn(#"Added Custom", "Custom.1", each List.Accumulate(

    List.Numbers(0, Table.RowCount(Table1)), 

    [Description], 

    (state, current) => 

       if Text.Contains (state, Table1[Fruit]{current}, Comparer.OrdinalIgnoreCase) then "Fruit" else state))

4. apply and close the power query editor

5 use the visual  

demov2 

[campelliann]

Hi @jiandaoz 

Try something like the approach below.
Make sure you add a measure filter in order for the table to only show the "Selected" result

Measure =

if ((CONTAINSSTRING(CONCATENATEX('Table 1','Table 1'[Fruit]),"apple") && CONTAINSSTRING(CONCATENATEX('Table 2','Table 2'[Description]),"apple")) ||

(CONTAINSSTRING(CONCATENATEX('Table 1','Table 1'[Fruit]),"orange") && CONTAINSSTRING(CONCATENATEX('Table 2','Table 2'[Description]),"orange")) ||

(CONTAINSSTRING(CONCATENATEX('Table 1','Table 1'[Fruit]),"banana") && CONTAINSSTRING(CONCATENATEX('Table 2','Table 2'[Description]),"banana")),"Selected","not selected")

[jiandaoz]

Thank you first! in my real example, I have hundreds of categories of "Fruit". Is there any other way I can do this easily?

[campelliann]

Hi there. Perhaps a more veteran member can come up with something in DAX. (Power query can also be useful).

I can make it work if the selection is only for 1 item... Meaning the user only selects "apple" or only selects orange: (you can use Values or concatenatex again)

CONTAINSSTRING(CONCATENATEX('Table 2','Table 2'[Description]),VALUES Table 1 [Fruit]))