Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Mighty_Andrew
Regular Visitor

Visualization filter based on selection that contains similar text in another column

Hello,

Im looking for the way to solve this case:

I want to choose in filter visualization [TABLE_X]Product = "Board", then visualization should show me every "Product" that has value in [TABLE_X]MPN like in selected Product.

For example:

If  [TABLE_X]Product = "Board" has in [TABLE_X]MPN ="5,7"  visualization should show me every product with 5 or 7.

 

Result:

 Board

Chair

Table

Pen

 

TABLE_X:

No.ProductMPN

1.

Pen1,3,6,8,7
2.Chair2,5,8,1,3
3.Board5,7
4.Pencil2,90,3,1
5.Cup 
6.Table1,23,5,3
7.Clip1,12,42

 

Thank you

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @Mighty_Andrew ,

Here are the steps you can follow:

1. Enter Power query, select [MPN], and click Home – Split Column.

vyangliumsft_0-1644546619441.png

2. Select [MPN.1], [MPN.2], [MPN.3], [MPN.4], [MPN.5] that form the table, and click Transform – Unpivot Columns.

vyangliumsft_1-1644546619445.png

Result:

vyangliumsft_2-1644546619448.png

3. Create calculated table.

Slice =
DISTINCT('Table'[Product])

vyangliumsft_3-1644546619449.png

4. Create measure.

Flag =
var _selectedvalue=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Product] in ALLSELECTED(Slice)),"1",'Table'[Value])
return
IF(MAX('Table'[Value]) in _selectedvalue,1,0)

5. Place [Flag]in Filters, set is=1, apply filter.

vyangliumsft_4-1644546619450.png

6. Result:

11.png

Best Regards,

Liu Yang

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

hi 

another solutions

1.make duplicate of the table

2.make a relation between table1[mpn] and table2[mpn]

3.add table1[product] to a slicer

4. add table1[mpn] and table2[product] to the table

esha_shah2002_0-1644585445724.png

if this post hepls ,accept it as a solution

Anonymous
Not applicable

hi 
use this measure

Measure 2 =
CONCATENATEX(ALL(Sheet1),if(Sheet1[MPN] = SELECTEDVALUE(Sheet1[MPN]),Sheet1[Product] & " "))
 
esha_shah2002_0-1644571832676.png

if this post hepls ,accept it as a solution

v-yangliu-msft
Community Support
Community Support

Hi  @Mighty_Andrew ,

Here are the steps you can follow:

1. Enter Power query, select [MPN], and click Home – Split Column.

vyangliumsft_0-1644546619441.png

2. Select [MPN.1], [MPN.2], [MPN.3], [MPN.4], [MPN.5] that form the table, and click Transform – Unpivot Columns.

vyangliumsft_1-1644546619445.png

Result:

vyangliumsft_2-1644546619448.png

3. Create calculated table.

Slice =
DISTINCT('Table'[Product])

vyangliumsft_3-1644546619449.png

4. Create measure.

Flag =
var _selectedvalue=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Product] in ALLSELECTED(Slice)),"1",'Table'[Value])
return
IF(MAX('Table'[Value]) in _selectedvalue,1,0)

5. Place [Flag]in Filters, set is=1, apply filter.

vyangliumsft_4-1644546619450.png

6. Result:

11.png

Best Regards,

Liu Yang

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

serpiva64
Solution Sage
Solution Sage

Hi,

if you want to obtain this result :

serpiva64_1-1644231045403.png

 

You can modify your query this way:

- duplicate your MPN column

- Slit your duplicated column by delimiter

serpiva64_2-1644231170231.png

- then in your canvas you can add a filter on single MPN.

 

If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.