How to add calculated column using DAX in Power Pivot

New DAX (Data Analysis Expression) Language is used to create calculated columns in Microsoft's Power Pivot. DAX formulas is compromised of an equal sign followed by a function or expression.Once the formula is created it is applied to the entire column.Column can only be recalculated if underlying data is refreshed or if manual recalculation is done.

I want to create a calculated column called Full_Name based on 3 other columns First_Name, Middle_Name and Last_Name.Concept here is very similar calucated columns using SSAS Data Source View, except here we use Microsoft's new DAX expresssion.In this example I also want to add condition such that if the middle name is empty then exclude else inculde Middlename.

I have a simple customer table which looks like this:



Next step is to fire Excel Instance with Power Pivot plug-ins and connect to SQL Database (in my case BlogDB).




Click Next button, now wizard prompts connect to SQL table.



Next screen choose table we can to import, in our case it is CustomerName table.



Now Click finish




Now you have successfully created a power pivot table. Next Step is create a calculated column based on FistName,LastName and MiddleName.

Open up power pivot table focus on a column where you want to apply DAX expression. Enter the following expression.

=CONCATENATE( [FirstName]&" ", CONCATENATE( IF( LEN([MiddleName])>1, LEFT([MiddleName],1)&" ", ""), [LastName]))



Once you execute the expression result looks like this.



Notice how middle name is skipped for Mary Peterson.Now we have created a calculated columns in Power Pivot table.Pivot table model can be consumed other excel sheet or linked with other Power Pivot models.

Explicit Conversion (Convert vs Cast ) in SQL Server

For years I have been using Convert and Cast function in T-SQL with out giving much thought. Recently I was asked about the difference, I knew Cast was ANSI complaint and Convert was more specific to SQL Server beside that I was stumped. So I decided to do little digging, my first concern was to see if there are any Performance differences..
I found this article by Nakul where he tried to test performance between cast and convert.
http://beyondrelational.com/blogs/nakul/archive/2011/01/03/cast-v-s-convert.aspx
It becomes clear from the test that there is not much performance difference between the two functions but there few more things you could do with Convert which Cast cannot. Especially in a reporting scenarios where Datetime/Money types needs to be formatted, for such cases convert function proves handy. Convert also can convert string types to datetime this becomes useful when parsing through text files.
So for me I would choose convert over cast beacause of extra formatting flexibility it provides.

Large Value Data Types (Varchar(max) vs Text)

Have you wondered which data type to use when you are assigned to store large size data in a table (larger than 8000 characters). Physical limitation for SQL Server is 8 K per row. To store anything larger than 8 K you will need to choose between Text and varchar(max). On the surface both look same but there is a fundamental difference.
First of all Text is legacy type and existed before SQL 2005 release. Text are always stored outside the table as LOB object. It can store up to 2 GB of data. But it has few shortcoming listed below..

- Cannot join on text column
- Cannot be used on where clause
- String manipulation is limited (sub string functions)

With SQL 2005 Varchar(max) and nVarchar(max) was introduced. It addresses the limitation of text in terms of join, search and string manipulation with out compromising on the size limit ( 2 GB).Also varchar(max) is stored in the table space when size is below 8 K, if size exceeds 8 K it is stored outside table space. Data retrieval from tablespace is much faster.According to Microsoft Text data types will be depricated from future releases. Avoid using this feature in new development work, and plan to modify appliation that currently use Text and nText types.
Naturally Varchar(max) is a better option and should be used when ever possible. Text data types should only be for backward compatibility.
For additional reading on Large Value Data Types
http://msdn.microsoft.com/en-us/library/ms17815(v=SQL.110).aspx

When to use varchar(max) in sql server?

When length is greater than 4000 character then it is recommended to use varchar(max) because from storage prespective there is no difference between varchar(max) and fixed lenght varchar(n) when n > 4000.In both the cases when length exceeds more than 4000 data row is replaced with a pointer to a seprate page. So it is recommended to store data as varchar(max) for larger values.