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




3 comments:

  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
  2. Thanks for sharing such wonderful information about SQL Jobs .I read your articles very excellent and the i agree our all points because all is very good information provided this through in the post.

    ReplyDelete
  3. Betting on sports online now is legal in Indiana - JTM Hub
    Betting 여수 출장안마 on sports 창원 출장마사지 online now is legal in Indiana. 양산 출장마사지 제주도 출장안마 In this article, we'll outline the legal 광주광역 출장샵 and regulated sports betting markets and how to bet on

    ReplyDelete