Convert MDX into DAX Part2
We can now move on to some useful functions for writing DAX as in query language. Please note that list of DAX function is very big but here we are trying to find out the functions which can be used to get data in the form of result set, same as MDX and DAX.
In this series next functions are following:
- The coolest function I’ll say. It gives you a power of creating calculated Column in a table (or tabular expression) at query Run time. I believe there is no way of doing this in MDX (can’t create an Attribute itself in MDX).
ADDCOLUMNS(<Table or Tabular Expression>, “<Calculated column Name1>”, <Expression for Calculated column1>,. . . . . . )
As in SQL we can create a new column while writing query.
For example (simplest as possible):
[FirstName], [MiddleName], [LastName]
, ([FirstName] + ' ' + [MiddleName] + ' ' + [LastName])
Part of expected Result is:
Now in DAX same query will be:
,CONCATENATE('Employee'[First Name], CONCATENATE(" ",
CONCATENATE('Employee'[Middle Name], CONCATENATE(" ",
I know that String manipulation in DAX is quite a job, but there is no other way.
Here if we do not use SUMMARIZE and EVALUATE on ADDCOLUMNS, it will give all the Columns in table Employee with added column as Full Name.
But if you just want to see one column out of your new table (with added column) use SUMMARIZE to be specific.
This function makes your life easy to get what you want at the level of Table, and solved many of my issues in SSRS reporting.
- Quite a function. You can search the values just as LIKE operator in SQL inside a column (an Attribute in case of Cube).
SEARCH(<text to be searched>, <Column or text to look for>, <Start with character number (1 onwards)>, <Return value if not found>)
Here argument 3 and 4 are optional.
We can use SEARCH with different Tabular Expression to use it same as SQL.
FILTER(VALUES('Product Subcategory'[Product Subcategory Name]),
SEARCH("Mount",'Product Subcategory'[Product Subcategory Name], 1, 0)
This query will result 0 in case no relevant data is found by SEARCH.
Condition (in SQL LIKE) DAX Expression ‘%Mount%’ SEARCH(“Mount”,’Product Subcategory'[Product Subcategory Name] ‘%bike’ SEARCH(“*ike”,’Product Subcategory'[Product Subcategory Name] ‘Moun%Bike’ SEARCH(“Moun*bike”,’Product Subcategory'[Product Subcategory Name]
I don’t need to give more example as * is replacement of % (in SQL) in DAX. Along with FILTER and CALCULATETABLE functions we can use search for getting required data. In MDX we use mostly INSTR VB function for same purpose but this function is too much costly in terms of Performance.
Posted on August 25, 2012, in DAX, SSRS Reporting and tagged ADDCOLUMNS, ADDCOLUMNS in DAX, Convert DAX to MDX, Convert MDX to DAX, DAX function ADDCOLUMNS, Example on ADDCOLUMNS, Example on SEARCH, Function in DAX, SEARCH, SEARCH in DAX. Bookmark the permalink. 1 Comment.