Build a Universal Search Slicer in Power BI

[danextian]

Power BI offers several slicer options, but each has limitations. Standard slicers support multiple fields only through hierarchies and still require manual selection, even with text search. The Text Slicer visual allows a “contains” search without selecting values, but it works on only one column and cannot search across multiple tables.

In this article, I’ll show how to achieve a similar free-text search experience in Power BI despite these limitations.

First approach

If the columns to be searched reside in the same table, they can be concatenated into a single column and used as the dimension in the Text Slicer visual. However, this approach is limited to a single table. In many real-world scenarios — especially with fact tables—only foreign keys are commonly available. As a result, it becomes impossible to search dimension attributes such as product SKU, model, description, or category directly.

Second approach

This approach builds a keyword search table that enables free-text filtering across multiple dimensions and selected fact-level fields using a single Text Slicer It requires a column in the fact table that uniquely identifies each row —if one doesn’t exist, an index column in the query editor works. Name the table UniversalSearch. In this blog, we’ll use the AdventureWorks dataset.

• Create a Keyword column in each dimension table. In Product, Customer, Territory, etc., concatenate the attributes users may search for (SKU, name, description, category).

• Select keys from the fact table. From the fact table, select the fact primary key and each foreign key that points to a searchable dimension. Assign a category label (Product, Customer, Territory).
• Include fact-level search values.  Add an extra entry for fact-only fields, such as SalesOrderLineKey, so sales orders can also be searched directly.
• Union all entries. Use UNION to combine all categories into a single table. 
• Resolve keywords. For dimension categories, use LOOKUPVALUE to retrieve the Keyword from the related dimension table. For fact-level entries, use the key itself as the keyword.

Below is a sample DAX  to create such UniversalSearch table. 

UniversalSearch = 
VAR _product =
    SELECTCOLUMNS (
        'Sales',
        "Sales Order Line", Sales[SalesOrderLineKey],
        "Key", Sales[ProductKey],
        "Category", "Product"
    )
VAR _territory =
    SELECTCOLUMNS (
        'Sales',
        "Sales Order Line", Sales[SalesOrderLineKey],
        "Key", Sales[SalesTerritoryKey],
        "Category", "Territory"
    )
VAR _customer =
    SELECTCOLUMNS (
        'Sales',
        "Sales Order Line", Sales[SalesOrderLineKey],
        "Key", Sales[CustomerKey],
        "Category", "Customer"
    )
VAR _SO =
    SELECTCOLUMNS (
        'Sales',
        "Sales Order Line", Sales[SalesOrderLineKey],
        "Key", Sales[SalesOrderLineKey],
        "Category", "Sales Order"
    )
VAR _unionAll =
    UNION ( _product, _territory, _customer, _SO )
RETURN
    DISTINCT (
        ADDCOLUMNS (
            _unionAll,
            "Keyword",
                SWITCH (
                    [Category],
                    "Product", LOOKUPVALUE ( 'Product'[Keyword], 'Product'[ProductKey], [Key] ),
                    "Customer", LOOKUPVALUE ( 'Customer'[Keyword], Customer[CustomerKey], [Key] ),
                    "Territory",
                        LOOKUPVALUE (
                            'Sales Territory'[Keyword],
                            'Sales Territory'[SalesTerritoryKey], [Key]
                        ),
                    "Sales Order", FORMAT ( [Sales Order Line], "0" 
                )
        )
    )
)

• Many-to-many relationship. The resulting table will contain multiple rows per SalesOrderLineKey. This column is used to create a relationship with the fact table, forming a many-to-many connection. Make sure the relationship is single-direction, flowing from UniversalSearch to the fact table.

Note:

• Although this approach works, it is not recommended for large semantic models or high-cardinality dimension tables (such as the Customer table in this example), as it can significantly increase the model size.
• Four PBIX files are attached, each representing a different approach to creating the UniversalSearch table. The recommended method is to build the table at the source, followed by Power Query. When this is not possible and Power Query performance becomes a bottleneck, the table can be created using DAX, as shown in the sample.

A vlog is availabe on YouTube in Taglish ( Tagalog/Filipino and English combination) -  https://youtu.be/Tx_s8bMKxzU 

Universal Search - PQ.zip

Universal Search - DAX.zip