Filer list of values in slicer

[kshah1609]

I am using bind parameters option to dynamically fetch the data by modifying the sql query in advanced editor. Till then it works. Now, the client wants to copy paste list of values in slicer, something like 50 vaues at once. For that i tried to use mass filter  visual but there is a limitation in this case as well, that is that if after mass filtering the values, (i have 2 slicers for same value, normal one with select all enabled and mass filters one). and the flow is that one would first filter values using mass filter, then click on select all in normal slicers, as there is no option to sync slicers through different pages, and then when click on select all, it should display the values typed in mass filter only, but instead it shows all values when clicked on select all.

how to resolve that?

in my sql query, i use the parameter to filter the data such that, if the slicer is selected, it would append the where clause else if select all is clicked, it would not. how to resolve such that it filters, the values that are visible in slicer while clicking on select all not, all the values in general) or sync the slicer accross the pages.

(for some reasons i can't see posted reply)

@Akash_Varuna 

These are the error that i get when i try to use custom visuals: 

also, my sql query: 

let
// Flags for 'SelectAll' values
flagcol1 = if List.Contains(col1, "__SelectAll__") then 1 else 0,
flagcol2 = if List.Contains(col2, "__SelectAll__") then 1 else 0,

// Process selected values for col1, col2, and MIN_InvoiceNumber
selectedcol1 = if Type.Is(Value.Type(col1), List.Type) then
if List.Contains(col1, "__SelectAll__") then "All"
else Text.Combine(List.Transform(col1, each "'" & Text.From(_) & "'"), ",")
else Text.From(col1),

selectedcol2 = if Type.Is(Value.Type(col2), List.Type) then
if List.Contains(col2, "__SelectAll__") then "All"
else Text.Combine(List.Transform(col2, each "'" & Text.From(_) & "'"), ",")
else Text.From(col2),

selectedcol3 = if col3 = "NULL" or List.Contains(col3, "__SelectAll__") then ""
else Text.Combine(List.Transform(col3, each "'" & Text.From(_) & "'"), ","),

// Check if any other filter is applied
hasOtherFilters = Text.Length(selectedcol3) > 0,

// Build the WHERE clause based on selection
whereClause =
if selectedcol1 = "All" and selectedcol2 = "All" then "WHERE 1 = 1"
else if selectedcol1 <> "All" and selectedcol2 <> "All" then "WHERE [Column1] IN (" & selectedcol1 & ") AND SUBSTRING([Column2], 1, 5) IN (" & selectedcol2 & ")"
else if selectedcol2 = "All" then "WHERE [Column1] IN (" & selectedcol1 & ")"
else if selectedcol1 = "All" then "WHERE SUBSTRING([Column2], 1, 5) IN (" & selectedcol2 & ")"
else "",

// Build the full SQL query
fullQuery = "SELECT * FROM your_table_name " & whereClause &
(if Text.Length(selectedcol3) > 0 then " AND [Column3] IN (" & selectedcol3 & ")" else ""),

// Execute the SQL query
Source = Sql.Database("your_server_name", "your_database_name", [Query=fullQuery])
in
Source which works fine while using normal power bi slicer (with select all enabled). now if i try to use both normal slicer and mass filter (as idk what else i can use), and try to paste value and click filter, it filters the same values in normal slicer drop down as well , along with select all option(as expected as it is on same page and is made from same column of table). now, if i click on the select all option, it shows me all data instead of showing only all the values that i enetred in mass filter. how to achive this? (I am using direct query and bind parameters to bring data)

[v-karpurapud · Accepted solution]

Hello @kshah1609 

Thank you for reaching out to the Microsoft Fabric Community Forum. We understand you are experiencing an issue with slicers.

Since this issue involves a custom visual and Power BI does not support syncing mass filter slicers, I recommend contacting the visual's publisher for further assistance. Additionally, if you require a dynamically updating central parameter table, this depends on how parameters are passed in SQL, as measures in Power BI cannot be directly used in SQL queries. For official guidance and potential solutions, please raise a Microsoft Support ticket detailing the slicer syncing issue and custom visual support.

Kindly submit the support ticket using the link provided below.

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

If my response has resolved your query, please mark it as the Accepted Solution to help others. Additionally, I would appreciate a 'Kudos' if you found my response helpful.

Thank you!

[kshah1609]

I get these error when i try to use custom visuals: 

[kshah1609]

I tried to use custom visuals but it is giving me errors like: 

also, my query is like:   

let
// Flags for 'SelectAll' values
flagcol1 = if List.Contains(col1, "__SelectAll__") then 1 else 0,
flagcol2 = if List.Contains(col2, "__SelectAll__") then 1 else 0,

// Process selected values for col1, col2, and MIN_InvoiceNumber
selectedcol1 = if Type.Is(Value.Type(col1), List.Type) then
if List.Contains(col1, "__SelectAll__") then "All"
else Text.Combine(List.Transform(col1, each "'" & Text.From(_) & "'"), ",")
else Text.From(col1),

selectedcol2 = if Type.Is(Value.Type(col2), List.Type) then
if List.Contains(col2, "__SelectAll__") then "All"
else Text.Combine(List.Transform(col2, each "'" & Text.From(_) & "'"), ",")
else Text.From(col2),

selectedcol3 = if col3 = "NULL" or List.Contains(col3, "__SelectAll__") then ""
else Text.Combine(List.Transform(col3, each "'" & Text.From(_) & "'"), ","),

// Check if any other filter is applied
hasOtherFilters = Text.Length(selectedcol3) > 0,

// Build the WHERE clause based on selection
whereClause =
if selectedcol1 = "All" and selectedcol2 = "All" then "WHERE 1 = 1"
else if selectedcol1 <> "All" and selectedcol2 <> "All" then "WHERE [Column1] IN (" & selectedcol1 & ") AND SUBSTRING([Column2], 1, 5) IN (" & selectedcol2 & ")"
else if selectedcol2 = "All" then "WHERE [Column1] IN (" & selectedcol1 & ")"
else if selectedcol1 = "All" then "WHERE SUBSTRING([Column2], 1, 5) IN (" & selectedcol2 & ")"
else "",

// Build the full SQL query
fullQuery = "SELECT * FROM your_table_name " & whereClause &
(if Text.Length(selectedcol3) > 0 then " AND [Column3] IN (" & selectedcol3 & ")" else ""),

// Execute the SQL query
Source = Sql.Database("your_server_name", "your_database_name", [Query=fullQuery])
in
Source                                                                                                                                                                       works with normal slicers in power bi (with select all enabled), but when i try to use both normal slicer and mass filter (for now as i do't know what else to use), and enter values in mass filter, click on filter then the same values are visible in normal power bi slicer along with select all (as expected), now if i click on select all, it instead of showing only the values i entered in mass filter, shows all the values

[v-karpurapud]

Hi @kshah1609 

Thank you for reaching out to the Microsoft Fabric Community Forum.

Regarding your query about the filters list of values in the slicer, we would like to confirm that the solution provided by @Akash_Varuna is accurate and should resolve the issue you are facing. If the issue persists, please share the sample data or PBIX file so we can better understand the problem and assist you more effectively.

If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.

Thank you!

[Akash_Varuna]

Hi @kshah1609 , Could you try this please 

• Update SQL Query
  Use the mass filter values as a parameter in your SQL query.
  SELECT * FROM YourTable WHERE (@SelectedValues IS NULL OR ColumnName IN (@SelectedValues))
  If no selection gets all the data 

• Custom Visuals
  Use visuals like Smart Filter Pro or Dynamic Filter Slicer for bulk filtering with better control.

• Slicer Sync Across Pages

  • Use Power BI's slicer sync to synchronize slicers between pages.
  • Alternatively, create a calculated column or DAX measure to mimic filtered behavior across slicers.
    If this post helped please do give a kudos and accept this as a solution 
    Thanks In Advance

[kshah1609]

@Akash_Varuna 

These are the error that i get when i try to use custom visuals: 

[kshah1609]

I get these errors when i try to use those custom visuals.

[girishthimmaiah]

Syncing Slicers Across Pages
Power BI has the ability to sync slices that are the same across pages; however, it does not allow for syncing of different types of slices, such as Mass Filter slicers and Standard slicers. The workaround is:

Method: Use a Central Parameter Table:
Create a central parameter table with user inputs as your list of user inputs (the list of values they paste in with the mass filter).
Use his table dynamically to filter both slicers across pages.
Let's step through it:
Parameter Table Creation:
DAX
Copy
ParameterTable = DISTINCT(YourMainTable[YourFilterColumn])
Bind Both Slicers to This Table:
Add both slicers (Mass Filter & Standard) using this ParameterTable.
Sync Slicers:
Go to View tab → Sync Slicers.
Confirm that the same slicer is synced across all pages (ParameterTable).
2. Make sure that 'Select All' responds with Mass Filter values
In normal situations, the behavior of 'Select All' in slicers removes the filter and then shows all possible values. In order to force it to respect only that which was mass-filtered, we have to tweak SQL and DAX logic as follows:

Approach: A conditional parameter used in SQL queries
Now modify your SQL query in such a way that it could understand when it should go into the 'Select All' mode but still keep input from the mass filter in place.

SQL Query Logic:
sql
Copy
WHERE
(
(@Parameter IS NOT NULL AND YourColumn IN (@ParameterList))
OR
(@Parameter IS NULL AND YourColumn IN (SELECT * FROM MassFilterTable))
)
In short,
If the user had chosen specific values through the slicers, it would use @ParameterList as the filtering criteria.
In short, if you click on 'Select All' (@Parameter IS NULL), then it goes back to using the values stored in your MassFilterTable.
3. Some final enhancement with DAX (if necessary)
If you're handling this via DAX and not SQL:

DAX
Copy
FilteredValues =
IF (
ISFILTERED('ParameterTable'[YourFilterColumn]),
CALCULATE(SUM('YourTable'[Value]), FILTER('YourTable', 'YourTable'[YourColumn] IN VALUES('ParameterTable'[YourFilterColumn]))),
CALCULATE(SUM('YourTable'[Value]), FILTER('YourTable', 'YourTable'[YourColumn] IN ALLSELECTED('MassFilterTable'[YourFilterColumn])))
)
This ensures that, when no slicer value is selected, the filtering still takes place based on the mass-filtered values.

[kshah1609]

I am handling that via sql, I have a question, that will central parameter table will dynamically update? as each time user might use different bunch of values. also, i have synced the normal slicers accross the pages, the problem was just with mass filter slicers (i checked it does not support syncing). I tried to handle by creating a measure and splitting values using comma, but that measure can't be anyway used in sql.