Dynamic input a list of values and filter these (multiple) values in the same column

[Anonymous]

Hello,

I have a practical issue. In Tableau it is quite obvious how to solve, but difficult in Power BI (by the way, I searched the internet, this forum and many topics - I did not find a satisfactory answer) and I can use only Power BI (in Tableau is a dedicated filter).

Case description:

• Power BI contains multiple visualizations using a table ('Products', 'Categories', 'Volume', others), as below but with >1000 rows. 
• The user would like to see all values (all visualizations) based on the input (filter) .... and the input might look like list of values (strings) in the column 'Products' or 'Categories'. For example: user inputs P1, P2, P3.......(most likely ~100 values to enter at once, however the list depends on the current business need).

Question:

• How to make this "dynamic input" of the list of values? (in theory it's most likely wildcard with many ORs but probably cannot be done in Power BI)
  • After the list of values is captured how to execute the actual filtering/matching within the column?

I checked possibility of using IN operator in DAX however I don't know if IN can be parametrizaied so user can input somewhere list of values and these values will be ingested into IN operator?

Maybe there is a solution to use R/Python script inside the visualization to capture list of values and match them with columns? In R it would be someting like datatable[Products %in% list_of_values,]

Products	Categories	Volume
P1	A	1
P2	B	2
P3	C	3
P4	D	1
P5	A	2
P6	B	3
P7	C	1
P8	D	2
P9	A	3
P10	B	1
P11	C	2
P12	D	3
P13	A	2
P14	B	2
P15	C	2
P16	D	3
P17	A	3
P18	B	3
P19	C	3
P20	D	3
P21	A	3
P22	B	3
P23	C	3
P24	D	3
P25	A	3
P26	B	3
P27	C	3
P28	D	3
P29	A	3
P30	B	3

[PrabhavatiMali]

Use the Mass Filter from Marketplace.

Tutorial Link : https://www.google.com/search?q=mass+filter+power+bi&rlz=1C1GCEB_enAU959AU959&oq=mass+filter+&aqs=ch...

[v-angzheng-msft]

Hi, @Anonymous 

Try to use the search box of the slicer to quickly locate the filtered value, and then create a bookmark to record the unfiltered data status to clear the filter. Click the three-dot menu of the slicer to show the search box.

You can put multiple fields in the slicer to make the data easier to view and filter. You can either directly click to filter or search to locate the value. If you want to clear the filter, click the created bookmark to clear the filter to get all the data.

result like below:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[Anonymous]

Hello,

Thank you @v-angzheng-msft for this option. However, it will not solve the problem.

Please consider entering P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11 .... P30 at one (single paste of the whole list). Unfortunately you can filter one Px at the time or maybe there is a way to enter whole list so the engine will recognize and filter accordingly? 

In Tableau you can simply copy list of values from Excel and enter in the filter (custom value filter).

[v-angzheng-msft]

Hi, @Anonymous 

You can hold down Ctri and click to select multiple items, or you can turn off the multi-select with CTRL in the format pane, and then you can directly click to select multiple items.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[Anonymous]

Hi @v-angzheng-msft,

thank you for an answer. However this is not solve the issue (in business practice).

The business case remains: how to paste list of values to Power BI filter field? =>

• The input list (from the buiness user) may contains 63, 93 or even 223 (this is up to the user) different and randomly ordered identifiers 
  • Selection using CTRL will be impossible to execute for the larger list
• Example how it's done in Tableau -> straighforwad and simple through "Custom Value List" https://www.thedataschool.co.uk/hashu-shenkar/tableau-filtering-using-the-custom-value-list 

To be more specific:

1. Please simulate table in Excel (as below)
   1. should contain 100,123 products (P1; P2; P3....P100,123)
2. Then, in the filter field in Power BI, enter whole "Input list" (at once) so that the report is filtered according to the values from the input list.

Products:

Products	Categories	Volume
P1	A	1
P2	B	2
P3	C	3
P4	D	1
P5	A	2
P6	B	3
P7	C	1
P8	D	2
P9	A	3
P10	B	1
P11	C	2
P12	D	3
P13	A	2
P14	B	2
P15	C	2
P16	D	3
P17	A	3
P18	B	3
…	…	…
…	…	…
P100123	B	3

Input list:

P78
P94
P125
P198
P398
P598
P798
P998
P1198
P1398
P1598
P1798
P344
P417
P490
P563
P636
P709
P3198
P3398
P3598
P3798
P3998
P4198
P198
P271
P344
P417
P490
P5398
P5598
P5798
P5998
P6198
P6398
P6598
P6798
P3198
P5687
P8176
P125
P198
P271
P344
P417
P490
P563
P636
P709

P198
P198
P159
P179

[v-angzheng-msft]

Hi, @Anonymous 

This is also achievable, but it looks a little complicated.
You can create a calculated table that contains the values you want to filter, or you can filter the list directly on the measure.

_InputList = DATATABLE("product",STRING,{
{"P84"},{"P12"},{"P19"},{"P39"},{"P59"},{"P79"},{"P99"},{"P119"},{"P139"},{"P159"},
{"P179"},{"P34"},{"P41"},{"P49"},{"P56"},{"P63"},{"P70"},{"P31"},{"P39"},{"P35"},{"P37"},
{"P33"},{"P41"},{"P19"},{"P27"},{"P34"},{"P41"},{"P49"},{"P53"},{"P55"},{"P57"},{"P59"},{"P61"},{"P63"},
{"P65"},{"P67"},{"P31"},{"P56"},{"P81"},{"P125"},{"P19"},{"P27"},{"P34"},{"P41"},{"P49"},{"P56"},{"P63"},{"P70"},
{"P198"},{"P198"},{"P159"},{"P179"}
})

You may need other tools such as Excel to add quotes or quotes and commas to the filtered value string.
It looks like this:

Then you can create a measure to determine whether it is the value you want to filter

_isIn = IF(MAX('Table'[Products])in VALUES(_InputList[product]),1,0)

or

_isIn2 = IF(MAX('Table'[Products]) in 
{
"P84","P12","P19","P39","P59","P79","P99","P119","P139","P159","P179","P34","P41","P49","P56","P63","P70","P31","P39","P35","P37",
"P33","P41","P19","P27","P34","P41","P49","P53","P55","P57","P59","P61","P63","P65","P67","P31","P81","P125","P19","P27","P34","P41","P49",
"P63","P70","P198","P198","P159","P179"
},1,0)

And then show items when measure is 1 in the filter pane.

When you need to get a different filter result, just modify the value of the list.

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[Anonymous]

Hi @v-angzheng-msft ,

thank you very much. Recently I have been thinking about this solution. I've tested it and works.

One item is still missing. Below, you hardcoded list of desited products in the "_InputList", but is there a way to parameterize "Value" parameter in the DATATABLE(), so we can avoid hardcoding inputlist and can take the inputlist from text field (example: text filter such as Power BI Text Filter)?   

_InputList = DATATABLE("product",STRING,{
{"P84"},{"P12"},{"P19"},{"P39"},{"P59"},{"P79"},{"P99"},{"P119"},{"P139"},{"P159"},
{"P179"},{"P34"},{"P41"},{"P49"},{"P56"},{"P63"},{"P70"},{"P31"},{"P39"},{"P35"},{"P37"},
{"P33"},{"P41"},{"P19"},{"P27"},{"P34"},{"P41"},{"P49"},{"P53"},{"P55"},{"P57"},{"P59"},{"P61"},{"P63"},
{"P65"},{"P67"},{"P31"},{"P56"},{"P81"},{"P125"},{"P19"},{"P27"},{"P34"},{"P41"},{"P49"},{"P56"},{"P63"},{"P70"},
{"P198"},{"P198"},{"P159"},{"P179"}
})

For example: 

Instead of hardcoding input list, an user will input a list into a particular text box on the dashboard:

"P84","P12","P19","P39"...

Higlighlted (blue) value to be parametrized:

.

[VilleK]

Hi! Did you ever get this solved?

[Anonymous]

Unfortunately no.

[parry2k]

@Anonymous I think there are custom visuals for slicers that allow entering the values, please check at the marketplace.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

@parry2k thank you for the answer. As far as I know slicer doesn't suppurt entering values. In this case the main issue is how to handle/enable user to enter multiple (e.g. ~100) values (e.g. strings) 😞

[parry2k]

@Anonymous the best is to create dimension table for each of selection, let's say you have two more tables, one with all the product and 2nd with all the categories (both tables contains unique values) and have a relationship with you main table, it will be 1 to many.

create a slicer from these new tables where users will select the values, visuals will update based on the selection. 

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡