cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jiandaoz
New Member

Create a Measure to check if any column text is contained

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.

 

jiandaoz_0-1643417600177.png

 

4 REPLIES 4
emjp
Frequent Visitor

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

 

emjp_1-1643486532528.png

 

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

 

emjp_2-1643486532535.png

 

4. apply and close the power query editor

5 use the visual  

emjp_3-1643486532540.png

demov2 

campelliann
Resolver I
Resolver I

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

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

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

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

 

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!