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:
