Slicer with high cardinality column

[Elieekh17]

Hello Team,

I need your insights and ideas please for a need from business I have a column product name from dim Product with high cardinality in dax studio and this column added to dropdown slicer , users claiming that when opening the slicer it takes 40seconds to show the data. How can I solve this issue. I can't remove duplicates and they don't want to filter by ID

Any ideas

Thank you

[GrowthNatives · Accepted solution]

Hi @Elieekh17 ,
This is a common performance issue in Power BI when using slicers on high-cardinality text columns (like Product Name).

⚙️ Why It’s Slow

When you open a slicer, Power BI must:

1. Query all distinct product names from the model (often thousands or more).

2. Sort and display them — which can take a long time if the column has:

   • Many distinct text values (high cardinality).

   • Long text strings.

   • A large model size or limited memory.
     
     

✅ Solution

1. Use a Supporting “Search Table”

Create a lightweight disconnected table containing only distinct product names.

In Power Query:

Products_List = 
SELECTCOLUMNS(
    DISTINCT('Dim Product'[Product Name]),
    "Product Name", [Product Name]
)

Then use this Products_List table in your slicer instead of Dim Product[Product Name].
Since it’s disconnected, it won’t trigger large model scans every time you open it.

To filter visuals, use a bridge measure:

SelectedProductFilter =
IF(
    ISFILTERED('Products_List'[Product Name]),
    CALCULATE(
        [Your Measure],
        TREATAS(
            VALUES('Products_List'[Product Name]),
            'Dim Product'[Product Name]
        )
    ),
    [Your Measure]
)

👉 This keeps the slicer light but still applies to your visuals.

2. Use a Hierarchical Slicer or Search Box

If product names can be grouped (e.g., Category → Subcategory → Product):

• Use hierarchical slicers — so users expand step by step instead of loading thousands at once.

• Enable the Search box on the slicer to let users type, instead of scrolling through all items.

3. Switch to a Dropdown or Power BI “Smart Filter” (from AppSource)

• Dropdown slicers only load visible items.

• Third-party visuals like Smart Filter Pro or Hierarchy Slicer handle large lists more efficiently.

4. Optimize the Model

If possible:

• Ensure Dim Product[Product Name] is stored as a Categorical data type, not a text with long strings.

• Avoid calculated columns that dynamically change — they prevent caching.

• Reduce model size by keeping only required columns in Dim Product.
  
  

5. Use a Parameter Table for Search

Create a what-if parameter or text input box that allows users to type a partial name.
Then filter the product table based on a measure (using SEARCH() or CONTAINSSTRING()), which can be more efficient than loading a massive dropdown.

💡 Quick Fix (If You Want Minimum Change)

If you don’t want to restructure much:

• Keep your slicer as dropdown, not list.

• Turn on Search.

• Sort Product Name ascending (not by another column).

• Enable “Single select” if possible — reduces cache operations.

• Pre-filter the page using another slicer (like Category) before Product slicer loads.

⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together!🚀 [Explore More]