Search for dimension members in DAX using the “search” function

If you want to search for specific dimension members you can use the “Search” function in DAX. The “Search” function also support wildcards.

So if you want sales for every products that contains the word “Mountain” you can write a query like this.

EVALUATE
FILTER (
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( 'Product'[Product Name] ),
            "Sum of Sales Amount", 'Internet Sales'[Internet Total Sales]
        )
    ),
    'Internet Sales'[Internet Total Sales] <> 0
        && SEARCH ( "*Mountain*", 'Product'[Product Name], 1, 0 ) <> 0
)

And the result will look like this

Search 1

Below is a more complex sample. It returns all products that contains the word “Mountain”. But it also returns the percentage of the total for all products containing the word “mountain”

DEFINE
    MEASURE 'Internet Sales'[Total] =
        CALCULATE (
            'Internet Sales'[Internet Total Sales],
            FILTER (
                ALL ( 'Product'[Product Name] ),
                SEARCH ( "*Mountain*", 'Product'[Product Name], 1, 0 ) <> 0
            )
        )
EVALUATE
FILTER (
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( 'Product'[Product Name] ),
            "Sum of Sales Amount", 'Internet Sales'[Internet Total Sales],
            "Percent of all mountain", FORMAT (
                DIVIDE (
                    'Internet Sales'[Internet Total Sales],
                    'Internet Sales'[Total]
                ),
                "Percent"
            )
        )
    ),
    'Internet Sales'[Internet Total Sales] <> 0
        && SEARCH ( "*Mountain*", 'Product'[Product Name], 1, 0 ) <> 0
)

And the results look like this

Search 2

Advertisements