DAX with Tabular Model Series | How to find multiple string in a tabular table using DAX?

Today let’s discuss how we can find multiple strings in a tabular model using DAX language. For this we need to use FIND function with CalculateTable or || with Filter. Well both filter and calculateTable are used like where clause in SQL Server. Now lets go through an example to discuss the same in more detail

--

EVALUATE
(
       CALCULATETABLE(
       SUMMARIZE
       (
                 InternetSales,
                 InternetSales[Types],
                 InternetSales[Description]
       ),
                 InternetSales[ClientId] = 124090
                 ,Month[Month] >= DateValue("01/01/2012")
		,Find ( "," & InternetSales[Description] & "," , ",Area Light,Cable Charges,Cable One Time   Chg,Charitable Donation,", 1, - 1 ) > - 1
       )
)

--

Ok so in the above example we are selecting types and description column from Internet Sales table. Please note that we are selecting distinct data only. Basically Summarize works like that. It will always gives distinct values. Calculate table is used to filter out things like where clause works in SQL Server. Here we have applied multiple filters.

Now point to be noted is how Filter function os used to find multiple strings from a single description column. Here we will get all the rows where column value matches with any of the value given in the string.

Now let’s consider one more query where we are finding multiple strings

--

EVALUATE
(
       SUMMARIZE(
              Calculatetable
              (
                        InternetSales
		        ,InternetSales[ClientKey] = 124090 ,
                        Month[Month] >= DateValue("01/01/2006") ,
                        FILTER
						(
							InternetSales,
							InternetSales[Description] ="Elec Adjustment" ||
							InternetSales[Description] ="Elec Off Peak" ||
							InternetSales[Description] ="Demand Off Peak" ||
							InternetSales[Description] ="Elec Partial Pk" ||
							InternetSales[Description] ="Dmd Partial Pk" ||
							InternetSales[Description] ="Garbage/yards" ||
							InternetSales[Description] ="Cust Chrg Water"
						)
              )
              ,InternetSales[Types]
	      ,InternetSales[Description]
       )
)

--

In the above query we are using Filter function with Or condition to find out multiple strings.

The second option of using Filter function with || condition to find out multiple strings is more efficient in terms of performance. It is more scalable and takes very less time to get the data.

DAX is somewhat different when it comes to understanding how the things are executed and the execution plan. I will try to cover things slowly so that people can understand DAX complexity easily.

Thanks for reading

-Pawan Khowal

MSBISkills.com