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:

ADDCOLUMNS:

  • 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):

    SELECT
    [FirstName], [MiddleName], [LastName]
    , ([FirstName] + ' ' + [MiddleName] + ' ' + [LastName])
    AS FullName
    FROM [dbo].[DimEmployee]
    Part of expected Result is:

    Now in DAX same query will be:

    EVALUATE
    SUMMARIZE(
    ADDCOLUMNS('Employee'
    ,"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.

SEARCH:

  • 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.
    Example:

    EVALUATE
    FILTER(VALUES('Product Subcategory'[Product Subcategory Name]),
    SEARCH("Mount",'Product Subcategory'[Product Subcategory Name], 1, 0)
    >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.

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: