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])
    AS FullName
    FROM [dbo].[DimEmployee]
    Part of expected Result is:

    Now in DAX same query will be:

    ,"Full Name"
    ,CONCATENATE('Employee'[First Name], CONCATENATE(" ",
    CONCATENATE('Employee'[Middle Name], CONCATENATE(" ",
    'Employee'[Last Name])
    ,[Full Name]

    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.
    Simplest Example:
    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)
    Result is:

    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 , , , , , , , , , . Bookmark the permalink. 1 Comment.

  1. This is really helpful!!!!!! I was breaking my head over the like operation and now I am relieved! 🙂 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: