Tuesday, July 30, 2013

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:

 

image

 

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.

 

image

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.

 

image

 

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:

 

image

 


Reference




1 comment:

  1. How do you search for substrings in a column value using DAX?

    Example: In column [Customer Name], and value "James Lee," what would the DAX statement be to specifically locate "ames" (from "James")?

    I still cannot figure this out...stumped.

    ReplyDelete