Use DAX Search Function to Retrieve Subset Data in SSRS Report

Most of you would be familiar with substring search in T-SQL. If you have a SSRS report using DAX, do you ever winder how to do the search using the pass in parameter?

Here is the sample DAX query used to retrieve the Order Count and Sales Amount for Customers from Internet Sales:

EVALUATE

SUMMARIZE (   

CALCULATETABLE(
    'FactInternetSales'    
        )
,DimCustomer[CustomerName]
,DimCustomer[Gender]
,"Order Quantity", [Order Count]
,"Sales Amount", [Sum of Sales Amount]

)

Here is the sample result:

 

 

If you only want to return certain customer, you will need to add a filter by pass in the Customer name as the report parameter.  Here is the sample DAX query:

 

 

EVALUATE

SUMMARIZE (   

CALCULATETABLE(
    'FactInternetSales',  DimCustomer[CustomerName] = @CustomerName
        )
,DimCustomer[CustomerName]
,DimCustomer[Gender]
,"Order Quantity", [Order Count]
,"Sales Amount", [Sum of Sales Amount]

)

Then you could get the result that has exact match to the customer name passed in.

 

If you need to return all the customers that has name like “Peter”, you could use DAX Search function to retrieve the list.  Here is the example:

 

EVALUATE

SUMMARIZE (   

CALCULATETABLE(
    'FactInternetSales',  
    IFERROR(Search(@CustomerName, DimCustomer[CustomerName]), -1) > 0
        )
,DimCustomer[CustomerName]
,DimCustomer[Gender]
,"Order Quantity", [Order Count]
,"Sales Amount", [Sum of Sales Amount]

)

 

The above query equals to the following T-SQL:

SELECT 
[CustomerName] ,[Gender], [Order Count],[Sum of Sales Amount]
FROM [dbo].[FactInternetSales] F 
INNER JOIN [dbo].[DimCustomer] C
On C.CustomerKey = F.CustomerKey
WHERE C.[CustomerName] like @CustonerName

You can see the result as below.  All the customers with name containing “Peter” got returned.

 

 

Sometime you may also want to handle the situation when user passed in Space or Blank parameter.  Here is a technique you could use.  When Blank customer name entered, the report will return all the customer without any filter.

EVALUATE

SUMMARIZE (   

CALCULATETABLE(
    'FactInternetSales',  
    IF(@CustomerName = blank(), 1=1, IFERROR(Search(@CustomerName, DimCustomer[CustomerName]), -1) > 0 )
                    )
,DimCustomer[CustomerName]
,DimCustomer[Gender]
,"Order Quantity", [Order Count]
,"Sales Amount", [Sum of Sales Amount]

)

 

Here is the result:

 

 

Reference

http://technet.microsoft.com/en-us/library/ee634235.aspx