Forum Discussion
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. | Product | MPN |
1. | Pen | 1,3,6,8,7 |
| 2. | Chair | 2,5,8,1,3 |
| 3. | Board | 5,7 |
| 4. | Pencil | 2,90,3,1 |
| 5. | Cup | |
| 6. | Table | 1,23,5,3 |
| 7. | Clip | 1,12,42 |
Thank you
Hi Mighty_Andrew ,
Here are the steps you can follow：
1. Enter Power query, select [MPN], and click Home – Split Column.
2. Select [MPN.1], [MPN.2], [MPN.3], [MPN.4], [MPN.5] that form the table, and click Transform – Unpivot Columns.
Result:
3. Create calculated table.
Slice = DISTINCT('Table'[Product])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.
6. Result:
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
4 Replies
- serpiva64
Solution Sage
Hi,
if you want to obtain this result :
You can modify your query this way:
- duplicate your MPN column
- Slit your duplicated column by delimiter
- 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 !
Hi Mighty_Andrew ,
Here are the steps you can follow：
1. Enter Power query, select [MPN], and click Home – Split Column.
2. Select [MPN.1], [MPN.2], [MPN.3], [MPN.4], [MPN.5] that form the table, and click Transform – Unpivot Columns.
Result:
3. Create calculated table.
Slice = DISTINCT('Table'[Product])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.
6. Result:
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
